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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE FUNCTION [dbo].[Split]( @delimited nvarchar(max), @delimiter nvarchar(100)) RETURNS @t TABLE( val nvarchar(max))ASBEGIN 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) RETURNEND |
You can use this function to split array of ID with IN statement:
1 2 3 | 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…
