Input paramter for Store Procedure like in statement 'Value1','Value2'

Question

Input paramter for Store Procedure like in statement 'Value1','Value2'

cuong.dangvinh

Grasshopper

Points: 21
More actions
June 20, 2014 at 1:37 am

#292520

Hi all,
How can I input parameter for Store Procedure like In ('Value1','Value2')
Example :
Create procedure GetUsers
@Users nvarchar(200)
as
Select * from Users where UserID in (@Users)
Many thanks,
Cuong:-)

Viewing 7 posts - 1 through 7 (of 7 total)

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

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 1

You will have to split the values coming in from the parameters.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

cuong.dangvinh Grasshopper Points: 21 More actions · Answer 2

Hi,

I can use dynamic TSQL, but with complex TSQL in procedure difficult to handle.

Thanks,

Cuong

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 3

No need for dynamic sql.Just split the values ('Value1','Value2') and insert into a temp table

Value1

Value2

then Select * from YourTable where Id in (Select Id from Temp)

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Or you could avoid the need for a table if you're using them just once.

Select *

from YourTable

WHERE Id in (Select Id from dbo.DelimitedSplit8K(@Parameter, ','))

Notice that I used the DelimitedSplit8K which you can find in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

cuong.dangvinh Grasshopper Points: 21 More actions · Answer 5

Hi Luis C and Sachin,

I done, thanks both of you so much.

Regards,

Cuong:-)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Thak you for the feedback.;-)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2