CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
WorkerThread
WorkerThread

Reputation: 2213

Select a scalar value from a table

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?

Upvotes: 29

Views: 58893

Answers (4)

Matthew
Matthew

Reputation: 10444

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.

Upvotes: 3

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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

Upvotes: 15

OMG Ponies
OMG Ponies

Reputation: 332581

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.

Upvotes: 46

Conrad Frix
Conrad Frix

Reputation: 52645

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

Upvotes: 5

Related Questions