Using SELECT to assign interdependent variables in SQL Server

Question

I have a rather long stored procedure which accepts input parameters, performs calculations and manipulations in accordance with a series of conditions, and then returns output parameters.

The problematic section is a SELECT statement which assigns values to multiple variables within the stored procedure, but these values are interdependent.

SELECT @varX = CASE WHEN @REC_CHANGE = 1 THEN VT.X ELSE @varX END
      ,@varY = CASE WHEN @REC_CHANGE = 1 THEN VT.Y ELSE @varY END
      ,@varZ = CASE WHEN @REC_CHANGE = 1 THEN VT.Y * VT.X ELSE @varX END
FROM VarTable VT
WHERE VT.ID = @myID
AND <....>

The @REC_CHANGE variable determines if the record to be examined is different than the user input values. If so, then we want the query to assign the value from the table. If not, then we want to keep the user input value.

The problem I'm encountering is that the first variable, @varX, always takes the user input value rather than reassigning the value from the table, even if the other variables just after it do take the values from the table. For example, @varY does pull the new value from the table when @REC_CHANGE = 1, but @varX does not. These values both come from the same table, from the same row with the same ID that is indicated in the WHERE clause.

As you can see, the condition of @REC_CHANGE does not differ between the assignments, so I'm having trouble understanding why @varX would stay the same (the user input value), while @varY gets updated accordingly. Moreover, because the later assignments are dependent upon @varX, they sometimes return false values.

I would appreciate any pointers you guys might provide. Thanks a ton in advance!

EDIT: As a note, I've checked what happens if I separate the @varX assignment into its own SELECT or SET statement with the exact same conditions, and the value updates accordingly. That is to say that the query looks like this:

SELECT @varX = CASE WHEN @REC_CHANGE = 1 THEN VT.X ELSE @varX END
FROM VarTable VT
WHERE VT.ID = @myID
AND <....>;

SELECT @varY = CASE WHEN @REC_CHANGE = 1 THEN VT.Y ELSE @varY END
      ,@varZ = CASE WHEN @REC_CHANGE = 1 THEN VT.Y * VT.X ELSE @varX END
FROM VarTable VT
WHERE VT.ID = @myID
AND <....>;

This returns the correct results, which leads me to wonder if SQL Server can't handle interdependent variables of this type?

Connell B. · Accepted Answer · 2015-08-30 08:09:41Z

1

What you're trying should work. Here's a self-contained example that I've tested on SQL Server 2008 and it delivers the expected result. I would consider that it might be another element of your query that is causing the unexpected behaviour.

DECLARE @REC_CHANGE AS bit
DECLARE @myID AS int
DECLARE @varX AS int
DECLARE @varY AS int
DECLARE @varZ AS int

SET @REC_CHANGE = 1
SET @myID = 1
SET @varX = 2
SET @varY = 3

SELECT @varX = CASE WHEN @REC_CHANGE = 1 THEN VT.X ELSE @varX END
  , @varY = CASE WHEN @REC_CHANGE = 1 THEN VT.Y ELSE @varY END
  , @varZ = CASE WHEN @REC_CHANGE = 1 THEN VT.Y * VT.X ELSE @varX END 
FROM
(SELECT 1 AS ID, 5 AS X, 6 AS Y) 
AS VT
WHERE @myID = VT.ID

SELECT @varX, @varY, @varZ

I'd suggest testing testing the above code in your environment. If it works then it might be worth looking at the other elements of your query and tables.

One possible solution would be to assign the user input parameters to 'dummy' parameters at the start of the query. so like:

DECLARE @varXb AS int
SET @vaxXb = @varX

answered Aug 30, 2015 at 8:09

Connell B.

261 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

3BK Over a year ago

You're right. The problem wasn't even in the stored procedure, but in the application passing the parameters. Everyone was so convinced that the problem was in the SP...thanks for the backup! :)

Collectives™ on Stack Overflow

Using SELECT to assign interdependent variables in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related