Stored Procedure parameter question.

Question

Stored Procedure parameter question.

kpao

SSC-Addicted

Points: 406
More actions
May 11, 2007 at 8:53 am

#117100

I have a problem about passing parameter to stored procedure.
Suppose the procedure like that:
Create Procedure Proc1
@CID varchar(30)
As
Select * from Customers Where CID IN (@CID)
GO
The question is how can I pass the value like '0000001', '0000004' to this procedure so that the query
become "Select * from Customers Where CID IN ('0000001', '0000004') "

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jus SSCrazy Points: 2536 More actions · Answer 1

Hope the following works. I have Hard coded @CID as set @CID ='0001,0004' inside proc which can be removed in your original proc.

---------------------------------------

create

Procedure Proc1

@CID

varchar(30)

As

declare

@vchrSQL varchar(300)

set

@CID ='0001,0004'

set

@CID=''''+@CID+''''

select

@CID = replace(@CID,',',''',''')

print

@CID

set

@vchrSQL ='Select * from Customers Where CID IN ('+(@CID) +')'

exec

(@vchrSQL)

GO

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

the Dynamic SQL above is really the only way to do this. the IN statement can't take a bunch of parameters or a comma list by itself.

Be sure when you pass the list in that you separate the items with commas and it is formatted as a valid string of values.

kpao SSC-Addicted Points: 406 More actions · Answer 3

Thanks for help. I found that there are another way to do this. This method is to create SQL function. This function is to split the CID into the table variable and return the table. So the stored proc can use this function to obtain multple CIDs. However, your method is simpler than mine.