Passing in string of values to stored proc. in 1 variable

Question

Passing in string of values to stored proc. in 1 variable

upstart

Ten Centuries

Points: 1255
More actions
January 26, 2011 at 8:14 am

#245866

Hi,
I know this a uber-newbie question, but for some reason, I cannot figure it out.
I have a simple stored procedure that takes in two variables, ‘id’ and ‘loc_id’, and uses them to select all records that match what was passed in. The problem I am facing is that the 2 variables could either be single values, NULL, or a string of values separated by commas.
i.e. exec sp_myproc ‘1526’, ‘0005’
or
exec sp_myproc ‘1526, 2552, 5523’, ‘0005,2452,0002’
The proc itself simply does something like this:
Select * from table
where id =@var1 and loc_id = @var2
When passing in single values, or null values, everything works fine. But I am having a hard time when I pass in a variable that has more than 1 value in the string.
I have tried something like this, but I cannot seem to get it to work for some reason. It returns no records no matter what I do.
CREATE PROC ......(
@ids varchar(500) ....
@loc_ids varchar(500)
)
BEGIN
DECLARE @SQL varchar(600)
SET @SQL =
'SELECT *
FROM dbo.table
WHERE ID IN (' + @ids + ')'
AND LOC_ID in (' + @loc_ids + ')'
EXEC(@SQL)
END
Any help would be appreciated.

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

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

Grant Fritchey SSC Guru Points: 398717 More actions · Answer 1

Take a look at this article by Jeff Moden[/url]. He shows how to use a tally table to solve the problem you're describing. It's a very efficient approach.

"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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

What you tried at first glance would seem inventive and probably work, not sure what the issue is.. It is also a method I hadn't thought of..

What I had done in the past is use a UDF I built nearly 10 years ago that takes a comma or any other specified value as a seperator and outputs a table of individual values as rows. Now you could either use that table directly or write it into a temp table which is then used to join into the main query. I'm having trouble finding the code at the moment.. Must be on my other machine..

The main reason why I would consider writing the output to a temp table instead of using it directly is that it outputs the equivalent of a table variable which has no statistics and if there are more than a few rows could greatly affect the query performance when joined to the base table.

I'm fairly sure you can find a UDF to do this, you are really breaking a csv list to a table..

CEWII

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

I had a UDF I used to use as well. With smaller data sets it works fine. But if you start to grow them, I really suggest using the tally table method. It's just light & day better.

"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

upstart Ten Centuries Points: 1255 More actions · Answer 4

Thanks for the help everyone. I ended up going with a UDF that parsed the string into individual rows in a temp table, then I just did a JOIN onto the table. Worked like a charm!