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
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
How to get comma separated Ids from sql server stored proc?
Answer: with help from terrific article
http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/
Stored Proc:
alter PROCEDURE [dbo].[myTable_CommaSeparatedIdsByStatus]
(@Status varchar(10))
AS
BEGIN
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + convert(varchar, Id)
FROM q_CoaRequest
where status = @Status
SELECT @listStr
End
Run Stored Proc:
exec myTable_CommaSeparatedIdsByStatus 'requested'
Result:
-----------------------------------------------------------
22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41