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?

score 46 · Accepted Answer · edited May 23 '17 at 12:09

46

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.

edited May 23 '17 at 12:09

Community

1
1

answered Jan 20 '11 at 21:17

OMG Ponies

325,700
82
523
502

@Conrad Frix: Corrected just before I saw your comment. Typo while I added the table alias. – OMG Ponies Jan 20 '11 at 21:19
@OMG Ponies. s'okay just wondering if it was something I hadn't seen before – Conrad Frix Jan 20 '11 at 21:20
@omg, @conrad - while that shows another way to write it, does it answer the question? – RichardTheKiwi Jan 20 '11 at 21:24
@cyberkiwi: I provided an example of proper syntax, and linked to a question comparing the use of both valid syntax -- how is that "not answering the question"? – OMG Ponies Jan 20 '11 at 21:26
@omg - How is the other form `set from subquery` not "proper"? I thought the crux of the question was `@configVar is set to NULL`. As far as I can tell, just switching the syntax won't change anything – RichardTheKiwi Jan 20 '11 at 21:29
@cyberkiwi: Where did I say that SET based on a subquery is not proper? The subquery is unnecessary based on the example, and it's the OP's decision (this being their question) to clarify further. Comment to clarify, but downvoting is hasty to me. – OMG Ponies Jan 20 '11 at 21:32
@Matthew - even OMG will testify that if the subquery `really` does return results, the SET form would have worked. – RichardTheKiwi Jan 20 '11 at 21:35
3

Sorry to have started an argument based on my foolish mistake! Even if my mistake was something that should have been caught immediately, I appreciate all your answers for helping me understand SQL better. – WorkerThread Jan 20 '11 at 21:41

RichardTheKiwi · Answer 2 · 2011-01-20T21:33:36.227

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

Using the `SET @configVar = (SELECT...);` format, do you have to use the parens? I'm wondering if that's why I got the syntax error that brought me here. — Kristen Hammack, Aug 02 '16 at 15:25
Yes you have to. Otherwise it is a syntax error. Syntax is what allows code to be precise in what is intended. — RichardTheKiwi, Aug 02 '16 at 22:57

score 5 · Answer 3 · answered Jan 20 '11 at 21:16

5

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

answered Jan 20 '11 at 21:16

Conrad Frix

51,984
12
96
155

Matthew · Answer 4 · 2011-01-20T21:44:53.483

3

You should do this instead:

SELECT @configVar = ExampleSetting FROM Settings WHERE SettingID = 1

Note that if your query returns more than one row your variable will equal the last returned value in the set.

edited Jan 20 '11 at 21:44

answered Jan 20 '11 at 21:16

Matthew

10,244
5
49
104

3

The statement is incorrect. It won't try to set it to "a set". It either works (null or value), or it will bomb (Subquery returned more than 1 value) – RichardTheKiwi Jan 20 '11 at 21:21

Select a scalar value from a table

4 Answers4