Dynamic stored procedure

bsa · October 12th, 2004, 02:28 AM

Hi All,
I have a stored procedure which has to select for eg. Customers from mastCustomer table on the basis of the Customers selected by the user in the frontend. While calling the procedure i am passing a string of IDs representing the Customers selected by the user.

The string is like this...
"1,2,4,5"
Now the ID column is the mastCustomer table is of type Int.

The foll. code is written in the stored procedure..

Create Procedure GetCustomer @strIDs varchar(100) as
Select * from mastCustomer where ID in @strIDs

The above code doesnt work. Can someone please help me.

Thanks,
BSA

ioates · October 12th, 2004, 02:38 AM

Hi
Not sure if this is the best way, but it works for me

CREATE PROCEDURE dbo.updLetterStatus
@members as varchar(1000)
AS
EXEC('SELECT * FROM tableName
WHERE membID IN (' + @members + ') ')

The @members string is pasted to the SP like '1, 2, 3'

Ian

happygv · October 12th, 2004, 10:05 PM

Hi BSA,

As COMMA is a delimeter for Parameters to procedures, I would suggest you not to pass comma in the @strIDs. This could be worked around in a way that you pass something else in place of COMMA and then within the procedure, convert that somethingelse back to comma.

Eg: From frontend, once the user selects the customers, replace all the comma with something like COLON.

Code:

'Not sure what frontend that you use. If that is ASP, then use
strSql = "Execute GetCustomer @strIDs='" & Replace(strSelectedCustomer, ",", ":") & "'"

So that the @strIDs would contain '1:2:3:4:6'.

Then within you procedure use the same replace function to convert those COLONs back to comma.

Code:

Create Procedure GetCustomer @strIDs varchar(100) as
   Select @strIDs=Replace(@strIDs, ':', ',')
   Select * from mastCustomer where ID in (@strIDs)
   -- Here the @strIDs would be converted back to '1,2,3,4,6'
   -- Also you are missing ()s around @strIDs.

This should be what you are looking for.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection