Matt Matt - 1 year ago 90
SQL Question

Stored Procedure is setting variable to value from wrong row

I am writing a stored procedure to query a specific table. The behavior I am seeing is really weird though. First let me layout my table design and some sample queries, to make this easier to explain:

Table Structure:

enter image description here

All Rows for specific Reservation ID (Only 1 Row)

enter image description here

Notice the value for the FieldName column, this will be important below.

Stored Procedure being called (and result):

enter image description here

Basically, this stored procedure returns the specified price (Flight price, Hotel price, Flight markup, Hotel markup, etc, etc..) and for the specified currency. All values are inserted into the Reservations_CurrencyPrices table, so they're only calculated once ever and not again (for performance reasons).

When debugging, I can see a problem, but I do not understand WHY this is happening:

enter image description here

Notice the value of the @Result variable is in the Locals window. This SHOULD NOT be... it makes no sense, because at this point any value for 'TotalMarkupPrice' record has not been inserted and indeed the value of the @Result variable is being set to the price from the 'TotalPrice' record.. which you can see in one of the images above where it's the only row in the table. So it seems that even though I have specified the

WHERE [FieldName] = @FieldName
condition and the value of
is 'TotalMarkupPrice', it returns the value from the row for 'TotalPrice' instead. This makes no sense. What am I missing here?

I ran that query manually to see what the result would be and it correctly returns NULL, as you can see below:

enter image description here

Why, oh why is @Result being set to the wrong value? The WHERE clause is not being honored when inside the sproc, it seems.

Answer Source

In your screenshot your code is:

WHERE [FieldName] = FieldName

, not what you think:

WHERE [FieldName] = @FieldName

Effectively, you are not using the @FieldName parameter.

Same problem one line below:

AND [ToCurrency] = ToCurrency

Since it is easy to miss one @ symbol, I prefer to name parameters like this:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download