Better than dynamic SQL - How to pass a list of comma separated IDs into a stored proc
- by Rodney Vinyard
Better than dynamic SQL - How to pass a list of comma separated IDs into a stored proc:
Derived form "Method 6" from a great article:
· How to pass a list of values or array to SQL Server stored procedure
· http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
Create PROCEDURE [dbo].[GetMyTable_ListByCommaSepReqIds]
(@CommaSepReqIds varchar(500))
AS
BEGIN
select * from MyTable q
JOIN
dbo.SplitStringToNumberTable(@CommaSepReqIds) AS s
ON
q.MyTableId = s.ID
End
ALTER FUNCTION [dbo].[SplitStringToNumberTable]
(
@commaSeparatedList varchar(500)
)
RETURNS
@outTable table
(
ID int
)
AS
BEGIN
DECLARE @parsedItem varchar(10), @Pos int
SET @commaSeparatedList = LTRIM(RTRIM(@commaSeparatedList))+ ','
SET @commaSeparatedList = REPLACE(@commaSeparatedList, ' ', '')
SET @Pos = CHARINDEX(',', @commaSeparatedList, 1)
IF REPLACE(@commaSeparatedList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @parsedItem = LTRIM(RTRIM(LEFT(@commaSeparatedList, @Pos - 1)))
IF @parsedItem <> ''
BEGIN
INSERT INTO @outTable (ID)
VALUES (CAST(@parsedItem AS int)) --Use Appropriate conversion
END
SET @commaSeparatedList = RIGHT(@commaSeparatedList, LEN(@commaSeparatedList) - @Pos)
SET @Pos = CHARINDEX(',', @commaSeparatedList, 1)
END
END
RETURN
END
