• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

SQL Server - passing a string paramether to a Stored Proc

I pass a string as a parameter to a stored procedure that should get results but does not..  My SP looks like this

@items nvarchar(50)

SELECT    dayofWeek, anotherItem
  FROM table1

where cast(month(dayofWeek) as nvarchar(20)) in (@items)


I get no results regardless of what I pass as a parameter.  For example if i pass '1,2,3' as a parameter, i get nothing.  What the heck have i done wrong?
2 Solutions
you'll need to do something like this....since it is a list of values, you'll need to use dynamic sql.  you can also use a function to split the values into a table...i'll show you the easier of the two:

declare @x nvarchar(2000)
set @x = 'SELECT    dayofWeek, anotherItem
  FROM table1 where cast(month(dayofWeek) as nvarchar(20)) in (' + @items + ')'

execute sp_executesql @x
Göran AnderssonCommented:
The string that you are using in the in clause is not considered to be a list of value, but just a single string.

in( '1,2,3' ) is not the same as in( '1', '2', '3' )

You can split the string into values and put in a temporary table, then join against the table to get the result.

As parsing the string verifies it's contents, it keeps the procedure from being wide open for SQL injection attacks.

alter procedure spMySP
   @items nvarchar(50)
declare @t table(m int)
declare @pos int
while (len(@items) > 0) begin
   set @pos = charindex(',', @items)
   if (@pos = 0) set @pos = len(@items) + 1
   insert into @t values (cast(substring(@items, 0, @pos) as int))
   set @items = substring(@items, @pos + 1, 1000)
select t1.dayOfWeek, t1.anotheritem
from table1 t1
inner join @t t on month(t1.dayOfWeek) = t.m

Open in new window

BoggyBayouBoyAuthor Commented:
Thanks !  Very nice solutions.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now