Menu
A way suggested by Mr. Dr_X is to pass a string of comma delimited integers as a parameter to a stored procedure, like '1,12,56,78'. Our stored procedure is responsible to split the string and extract each value and then use the values in its query.
While using older versions of SQL Server, I've used to the XML method to pass array or list to stored procedure. In the latest versions of SQL Server, we can use the User Defined Data Type (UDT) with a base type of table to send array or list through a parameter.
If you pass the comma separated (any separator) string to store procedure and use in query so must need to spit that string and then you will use it. Use dbo. ufnSplit function that I answered is better and clear code on store procedure.
So I have a Sybase stored proc that takes 1 parameter that's a comma separated list of strings and runs a query with in in an IN() clause:
CREATE PROCEDURE getSomething @keyList varchar(4096)
AS
SELECT * FROM mytbl WHERE name IN (@keyList)
How do I call my stored proc with more than 1 value in the list? So far I've tried
exec getSomething 'John' -- works but only 1 value
exec getSomething 'John','Tom' -- doesn't work - expects two variables
exec getSomething "'John','Tom'" -- doesn't work - doesn't find anything
exec getSomething '"John","Tom"' -- doesn't work - doesn't find anything
exec getSomething '\'John\',\'Tom\'' -- doesn't work - syntax error
EDIT: I actually found this page that has a great reference of the various ways to pas an array to a sproc
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With