stored procedure with IN

How would i call it?

Call the SP as follow:

EXEC sp_SPName '123,135,246,456'

Select allOpen in new window

If you expecting strings then you need to double quote them:

EXEC sp_SPName ''ABC'',''DEF'',''GHI'''

Select allOpen in new window

I attached my query any clue what is going wrong?

You forgot to attach it.

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."

query attached
query.JPG

As I said before you need to double quote strings:

Exec spFilmCertificate '''u'''
exec spFilmCertificate '''u''' ,'''p''' 

Select allOpen in new window

That is giving me the same result.

I noticed that the query i uploaded is showing in red ..perhaps i am doing something wrong?

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

Select allOpen in new window

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.

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

Select allOpen in new window

Got it ... thanks

ty