Setting a variable to the result of a select query SQL server 2012

Question

I am trying to find the most economical way of achieving a way to return the data I want and also updating another table within the same Stored Procedure.

I have drastically simplified my SQL below to illustrate my issue. Here's what I want to achieve :

DECLARE @UserID INT
SELECT TOP(1) @UserID = UserID, UserName, email, (#Loads of other columns#) FROM Users
UPDATE Logins SET LoggedIn = 1 WHERE UserID = @UserID

I understand I could do this by making sure that all returned columns are assigned to a local variable, but there are too many to be an efficient SPROC.

I don't want to have to do the SELECT statement twice (once to return the data and once to set the variable, ready for the update statement)

Any suggestions guys ? Thanks, Scott

Do you mean economical as in server performance or as in shorter, neater code? — bendataclear
– bendataclear, Commented Oct 24, 2012 at 12:41
A bit of both I guess, maybe just what you would consider to be the "best" way. This SPROC is likely to be hit several thousand times per day... — wotney
– wotney, Commented Oct 24, 2012 at 12:43

bendataclear · Accepted Answer · 2012-10-24 12:55:33Z

1

You could use OUTPUT to get values to a local table variable but you still have to use a local SELECT to get a single value from the table variable.

DECLARE @TBL TABLE(userid int, username varchar(50), email varchar(50), logged bit)

DECLARE @userid int

UPDATE TOP (1) Users
SET logged = 1
OUTPUT deleted.* INTO @TBL

SELECT top (1) @userid = userid from @TBL

SELECT @userid

Fiddle Example

answered Oct 24, 2012 at 12:55

bendataclear

3,8574 gold badges34 silver badges58 bronze badges

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

2 Comments

wotney Over a year ago

From a server performance point of view - Do you think creating a temporary table would be more efficient than either creating lots of variables or just making 2 select statements ?

bendataclear Over a year ago

Not sure how it would effect local performance, but it would only need to open a single transaction which would be a bonus, I guess it would decrease reads but increase cpu load slightly. Not got a machine available I can test it with sadly.

Collectives™ on Stack Overflow

Setting a variable to the result of a select query SQL server 2012

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related