Is there a better way to set local variables in a stored procedure?

Question

Is there a better way to set local variables in a stored procedure?

andy.gear

SSC-Addicted

Points: 493
More actions
January 16, 2012 at 3:16 pm

#391324

I want to set a bunch of variables to the results of a SELECT that will only return one line. Is there a better way to do it than the following?
DECLARE @Set int
DECLARE @Rarity decimal(5,3)
DECLARE @GrassType bit
DECLARE @WaterType bit
DECLARE @PsychicType bit
SELECT @Set=Version, @Rarity=Rarity, @GrassType=Grass, @WaterType=Water, @PsychicType=Psychic FROM Pokemon WHERE Name=@Pokemon

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 1

Not that I'm aware of. I assign variables in a select statement if I'm working with multiple variables that can be retrieved in one pass. If I'm assigning 1 variable or have multiple variables that can't be set in one select statement I use the set with subselect.

Mike Palecek SSCommitted Points: 1560 More actions · Answer 2

I agree, this looks to be the best way to assign this. Since you are coming from a select statement that is about as basic as you can get.

andy.gear SSC-Addicted Points: 493 More actions · Answer 3

Okay, thanks. For some reason I had it in my brain that this wasn't safe or bad form or something. Just me being paranoid I suppose.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

I don't think there is a better way but I have seen a lot of ways that are a lot worse. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 5

Just be 100% sure your select will only return 1 row. If it returns more than 1 it won't throw an error like the set clause you're probably more familiar with will. Which could be pretty bad depending upon what the rest of your code does with those variables.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

You can make it a little cleaner:

DECLARE @MyParam1 VARCHAR(50) = '42',

@MyParam2 INT = 42,

@MyParam3 TINYINT = 2 ;

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 7

Celko: Are you saying we should be able to do the following?

DECLARE@OneVARCHAR(20),

@TwoVARCHAR(20),

@ThreeVARCHAR(20)

SET(@One, @Two, @Three) = ('One', 'Two', 'Three')

SELECT@OneAS One,

@TwoAS Two,

@ThreeAS Three

I get an error when using paretheses after the SET command, whether I am trying to set a single variable or multiple variables. I have also tried including and excluding the "@" in the variable name; I get the same error either way.

What am I missing?

Paul White SSC Guru Points: 150467 More actions · Answer 8

fahey.jonathan (1/17/2012)
What am I missing?

Joe is referring to a 'standard' SQL syntax that is not yet supported in SQL Server.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 9

CELKO (1/17/2012)
The ANSI syntax is: SET <variable list> = <expression list>;
SET (a,b,c) = (1,2,3),
But another way is the row value constructor:
VALUES (1,2,3) AS X ( a,b,c)

Joe seems to be answering the question of how to set local variables in t-sql on SQL Server 2008, showing two methods, one using the SET with parentheses and one using a VALUES clause. If neither of these methods is possible on SQL Server 2008, it would be helpful to clarify that.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

CELKO (1/17/2012)
Please tell me that you do n ot really use BIT flags in SQL. That mess was 1950's assembly language, before we had high level abstract languages. .

Interesting. So what do you use to hold simple True/False logic? And if you don't use it, how you avoided the use of simple True/False logic in every table you've ever designed?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)