I'm wondering the way i can pass either an ArrayList, List
<int> or StringBuilder comma delimited list to some saved procedure so that I've found a listing of IDs using IN():
@myList varchar(50) SELECT * FROM tbl WHERE Id IN (@myList)
In C# I'm presently building their email list like a string that is comma delimeted however, if using nvarchar(50) for instance, because the type for that param within the saved procedure - I recieve a mistake because it can't convert '1,2,3' to int so it needs between your IN().
Any ideas? Much appreciated.
You could utilize a person Defined function for example
CREATE function [dbo].[csl_to_table] ( @list nvarchar(MAX) ) RETURNS @list_table TABLE ([id] INT) AS BEGIN DECLARE @index INT, @start_index INT, @id INT SELECT @index = 1 SELECT @start_index = 1 WHILE @index <= DATALENGTH(@list) BEGIN IF SUBSTRING(@list,@index,1) = ',' BEGIN SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT) INSERT @list_table ([id]) VALUES (@id) SELECT @start_index = @index + 1 END SELECT @index = @index + 1 END SELECT @id = CAST(SUBSTRING(@list, @start_index, @index - @start_index ) AS INT) INSERT @list_table ([id]) VALUES (@id) RETURN END
Which accepts an nvarchar comma separated listing of ids and returns a table of individuals ids as ints. After that you can join around the came back table inside your saved procedure like so -
DECLARE @passed_in_ids TABLE (id INT) INSERT INTO @passed_in_ids (id) SELECT id FROM [dbo].[csl_to_table] (@your_passed_in_csl) SELECT * FROM myTable INNER JOIN @passed_in_ids ids ON myTable.id = ids.id
In SQL 2008 you will find table-valued-parameters, which make an amiable option to parsing CSV see for an example.
Otherwise, an alternative choice is xml - the
xml data key in SQL Server enables you to definitely look at this pretty easily (even though it takes more transfer bytes).
I am unable to offer the actual code, however i have solved this issue by writing a function that can take the varchar and returns a table of ints.
After that you can do that ....
SELECT * FROM tbl WHERE Id IN (SELECT IntValue FROM dbo.CsvToInt(@myList))