May 11, 2007 at 8:53 am
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') "
May 11, 2007 at 9:43 am
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,',',''',''')
@CID
set
@vchrSQL ='Select * from Customers Where CID IN ('+(@CID) +')'
exec
(@vchrSQL)
GO
May 11, 2007 at 10:00 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 11, 2007 at 10:10 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply