All of the web applications that i am responsible to maintain have lots of stored procedures. And to many of them builds a SQL string and executes at the end of the procedure. Previous programmers wrote these SPs in that way because they don’t know how to handle comma separated ID values. For these cases a small table valued split function comes to help.
CREATE FUNCTION [dbo].[Split]
(
@delimited nvarchar(max),
@delimiter nvarchar(100)
) RETURNS @t TABLE
(
val nvarchar(max)
)
AS
BEGIN
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
select
r.value('.','varchar(5)') as item
from @xml.nodes('//root/r') as records(r)
RETURN
END
You can use this function to split array of ID with IN statement:
SELECT * FROM dbo.Split(N'a,b,c,d,e', ',')
SELECT * FROM dbo.Split('1,3,6,77,34,22', ',')
select * from Members where id in (SELECT Val as Id FROM dbo.Split('2707,2708', ','))
Happy coding…
