Using a variable in SQL script (Declare @x)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using a variable in SQL script (Declare @x)

Author

Topic

Alexander Barnes
Starting Member

10 Posts

Posted - 2007-12-30 : 13:25:40

I am not sure why the following does not work...

I am declaring a variable to hold a string to be used in my script. The contents of the variable looks perfect and works independently, the variable it does not work. (BTW, do you call this a variable in T-SQL too?). The idea is to pass the value to the script to generate different results sets.

//Parameter
DECLARE @Codes varchar(8000);
SET @Codes = '''07-1110_CHA,1'',''07-1110_DCV,2''';

//Examine contents - results: '07-1110_CHA,1','07-1110_DCV,2'
Select @Codes;

//Works fine
Select screening_cd,check_amount
From accounting
Where screening_cd in ('07-1110_CHA,1','07-1110_DCV,2');

//This does not work
Select screening_cd,check_amount
From accounting
Where screening_cd in (@Codes);

TIA

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-30 : 13:28:58

because it doesn't work that way. the variable is parsed as one value and not a set of values.
search here for split function that will split your string and return a resultset that you'll have to join to your original table.

or use dynamic sql but be aware of the sql injection problem.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-31 : 05:00:43

If you pass small set of data,

Select screening_cd,check_amount
From accounting
Where ','+@Codes+',' like '%,'+cast(screening_cd as varchar(20))+',%'

Madhivanan

Failing to plan is Planning to fail

Alexander Barnes
Starting Member

10 Posts

Posted - 2007-12-31 : 13:04:52

Thank you for your help. A very good new year to you.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-31 : 13:20:28

likewise.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources