I thought of sharing this simple function that you can use for splitting a list or nvarchar(max) or anything that is coma separated and sent to DB, into a temp table.
Performance is a bottle neck for us. As for that matter, for any SP executions. We cannot live with the IN operator. It is always advicable to go ahead with SET based operations like JOINs in these cases. They also have a good track record of showing an improved performance to a greater extend.
If you need to have such a list in a table format, it is always advicable to have a generic function in your DB to handle such operations, as you might have such scenarios coming up in other SPs also.
Adding the SplitList Function to fulfil the above requirement is shared below:
CREATE FUNCTION [dbo].[SplitList]
RETURNS @RtnValue table
Id int identity(1,1),
Insert Into @RtnValue (value)
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
–declare @tempList nvarchar(max)
–set @tempList = ‘1,2,3,4,5,6,7,8,9,10’
–select VALUE FROM dbo.SplitList(@tempList,’,’) — Remember, this will give you a table.. so store this statement into a temptable
Run the above function in your DB and you can start using the split list operation. Check the last 3 statements to see the usage of this function.
You normally should do
INSERT INTO @tbl_MyTempTable (ListValue)
SELECT VALUE FROM dbo.SplitList(@tempList,’,’) — my list is a coma separated one..
The above @tbl_MyTempTable will have my list (‘1,2,3,4,5’) stored as each rows..
Hope this was helpful…