Select a scalar value from a table

Question

Edit: The problem I had stemmed from a parameter-order mixup in my code. I sincerely appreciate everyone's help; my SQL understanding gets better each visit to SO.

I am writing a stored procedure which needs to select a bit of information from another table in order to do its job.

DECLARE @configVar int; SET @configVar = (SELECT ExampleSetting FROM Settings WHERE SettingID = 1); -- do something with @configVar to get the final result set 

Obviously (to people with a better understanding of SQL), the above is incorrect. No error, except when the stored procedure is executed, @configVar is set to NULL. I have double checked the table I'm SELECTing from and have ensured that the data exists.

Can someone show my where my misunderstanding is, and how I should correct it? It seems like this might be a common idiom; how is this normally accomplished?

Answer 1

TSQL allows you to set variables in the SELECT clause, using:

SELECT @configVar = s.examplesetting    FROM SETTINGS s  WHERE s.settingid = 1 

This assumes there's only one record in the table where the settingid value is 1.

See question "TSQL - SET vs. SELECT when assigning variables?" for more information on usage.

Answer 2

Obviously (to people with a better understanding of SQL), the above is incorrect.

Why would you say that? It is perfectly valid, even if it can be written as

SELECT @configVar = ExampleSetting FROM Settings WHERE SettingID = 1; 

In fact, it will give you an error in the first form when there are multiple rows returned (try the next query) which lets you know something is not what you expect, whereas the 2nd query above silently works by using the ExampleSetting value from the last matched record.

DECLARE @configVar int; SET @configVar = (SELECT number FROM master..spt_values); -- Msg 512, Level 16, State 1, Line 2 -- Subquery returned more than 1 value. 

Run this on its own and you may see something that surprises you

SELECT ExampleSetting FROM Settings WHERE SettingID = 1 

If this returns no records, that's why SET @configVar is left NULL

FYI, this is what I tried and it worked as advertised

DECLARE @configVar int; SET @configVar = (SELECT top 1 number FROM master..spt_values); select @configVar; -- result: -32768 

One exception to why you would use the SELECT @var form instead of SET from subquery form is that the first leaves the variable alone when the query finds no matching rows, the 2nd explicitly sets it to null.

DECLARE @configVar int; SET @configVar = 123; SET @configVar = (SELECT top 1 number FROM master..spt_values where 1=0); select @configVar;  -- @configVar was set to NULL  SET @configVar = 123; SELECT top 1 @configVar = number FROM master..spt_values where 1=0; select @configVar;  -- @configVar is LEFT as 123