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

ALTER PROCEDURE [dbo].[spMySP]
      
@items nvarchar(50)
      
AS

SELECT    dayofWeek, anotherItem
  FROM table1

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

END

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?
LVL 1
BoggyBayouBoyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
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
0
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)
as
 
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)
end
 
select t1.dayOfWeek, t1.anotheritem
from table1 t1
inner join @t t on month(t1.dayOfWeek) = t.m

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BoggyBayouBoyAuthor Commented:
Thanks !  Very nice solutions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.