passing variable and value

Question

Post reply

passing variable and value

Red8Rain

Hall of Fame

Points: 3312
More actions
April 29, 2008 at 12:38 pm

#206817

I have the following piece of code. I have seen it done before but never paid much attention to it until now. I have 95% of it done but the last 5% is not coming to me:
Declare @value_list varchar(500)
, @sql nvarchar(max)
Set @value_list='attribute, catalog_object'
set @sql = 'Select count(*) from tranrepl
Where articleName in (''' + @value_list + ''')'
print @sql
exec (@sql)
The above code gives me the output of:
Result: 0
SQL statement: Select count(*) from tranrepl Where articleName in ('attribute, catalog_object')
I know why it is giving me a result of 0 because I need the where clause to look like:
WHERE articleName IN ('attribute','catalog_object')
I can do it like this:
Declare @value_list varchar(500)
, @sql nvarchar(max)
Set @value_list='attribute' + '''' + ',' + '''' + 'catalog_object'
set @sql = 'Select count(*) from tranrepl
Where articleName in (''' + @value_list + ''')'
print @sql
exec (@sql)
But it is ugly you see.
Thanks.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Red8Rain Hall of Fame Points: 3312 More actions · Answer 1

figured it out

Declare @value_list varchar(500)

, @sql nvarchar(max)

Set @value_list = '''attribute'', ''catalog_object'''

set @sql = 'Select count(*) from tranrepl

Where articleName in (' + @value_list + ')'

print @sql

exec (@sql)

output: Select count(*) from tranrepl where articleName in ('attribute', 'catalog_object')

-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.

Jeff Moden SSC Guru Points: 1003913 More actions · Answer 2

Um, where will be be getting @Value_List from? A GUI? If so, you've just opened yourself up to a nasty little case of SQL Injection.

Lemme know... there are better ways.

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

Grant Fritchey SSC Guru Points: 398716 More actions · Answer 3

Lots better.

If nothing else, instead of dynamic queries, use a table valued function to pivot your comma delimited list into a table and then join against it. It's not always the fastest way to go, but it's easy to do & understand.

"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