stored procedure with IN

How would a where clause of a stored procedure with IN in the where clause where a list of parameter could be passed in??
vbnetcoderAsked:
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to create the SQL statement dynamically:
DECLARE @MySelect VARCHAR(MAX)

SET @MySelect = 'SELECT * FROM TableName WHERE ColName IN (' + @ListOfValuesParam + ')'

EXEC sp_executesql @MySelect

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
vbnetcoderAuthor Commented:
How would i call it?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Call the SP as follow:
EXEC sp_SPName '123,135,246,456'

Open in new window

If you expecting strings then you need to double quote them:
EXEC sp_SPName ''ABC'',''DEF'',''GHI'''

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

vbnetcoderAuthor Commented:
I attached my query any clue what is going wrong?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You forgot to attach it.
0
vbnetcoderAuthor Commented:
When i call it like this:


Exec spFilmCertificate 'u' --- i get the error "Incorrect syntax near the keyword 'IN'."

When i call it like this

exec spFilmCertificate 'u' , 'p' -- i get the error "Procedure or function spFilmCertificate has too many arguments specified."
0
vbnetcoderAuthor Commented:
query attached
query.JPG
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
As I said before you need to double quote strings:
Exec spFilmCertificate '''u'''
exec spFilmCertificate '''u''' ,'''p''' 

Open in new window

0
vbnetcoderAuthor Commented:
That is giving me the same result.

I noticed that the query i uploaded is showing in red ..perhaps i am doing something wrong?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Must be some missing single quote. In your stored procedure instead of executing the query, print it so we can see what's wrong:
'--EXEC sp_executesql @MySelect
PRINT @MySelect

Open in new window

0
vbnetcoderAuthor Commented:
When i run this: Exec spFilmCertificate '''u'''

I get this

SELECT  dbo.tblFilm.FilmName, dbo.tblCertificate.CertificateName, CertificateID
FROM            dbo.tblFilm INNER JOIN
                         dbo.tblCertificate ON dbo.tblFilm.FilmCertificateID = dbo.tblCertificate.CertificateID
Where CertificateID = IN ('u')

------------
When i run this: exec spFilmCertificate '''u''' ,'''p'''

I get the error Msg 8144, Level 16, State 2, Procedure spFilmCertificate, Line 0
Procedure or function spFilmCertificate has too many arguments specified.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, like I told you before. You are passing 2 parameters ('u' and 'p') and you should only pass one as a single string (I removed the extra single quotes near the comma):
exec spFilmCertificate '''u'' ,''p''' 

Open in new window

0
vbnetcoderAuthor Commented:
Got it ... thanks
0
vbnetcoderAuthor Commented:
ty
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 2008

From novice to tech pro — start learning today.

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.