Setting multiple variables for one column in SELECT statement

Question

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?

Pரதீப் · Accepted Answer · 2017-01-05 16:30:05Z

4

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' )

answered Jan 5, 2017 at 16:30

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gloria Santin Over a year ago

Perfect! I could not figure out how to use CASE...adding MAX was the ticket. Thanks!

Collectives™ on Stack Overflow

Setting multiple variables for one column in SELECT statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related