CODEWITHSUNDEEP
sqlsql-server
Gloria Santin
Gloria Santin

Reputation: 2136

Setting multiple variables for one column in SELECT statement

I have to set several variables in a stored procedure in SQL Server. Currently, I use individual SELECT statements to set each variable. I don't know if there is a better/ more efficient way of doing it. This is a sample of 2 variables but there are 8 variables total.

DECLARE @source nvarchar(250)
DECLARE @target nvarchar(250) 

SET @source = (SELECT Value FROM ApplicationSetting WHERE Key = 'Source')
SET @target = (SELECT Value FROM ApplicationSetting WHERE Key = 'Target')

Currently, I use 8 individual select statements to set each variable. Is this the most efficient way to set this many variables?

Upvotes: 4

Views: 2243

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

In a single select we can assign both the variables using conditional aggregate

Try this way 

DECLARE @source NVARCHAR(250)
DECLARE @target NVARCHAR(250)

SELECT @source = Max(CASE WHEN KEY = 'Source' THEN Value END),
       @target = Max(CASE WHEN KEY = 'Target' THEN Value END)
FROM   ApplicationSetting
WHERE  KEY IN( 'Source', 'Target' )

Upvotes: 4

Related Questions