Select comma separated result from via comma separated parameter
- by Rodney Vinyard
Select comma separated result from via comma separated parameter
PROCEDURE [dbo].[GetCommaSepStringsByCommaSepNumericIds]
(@CommaSepNumericIds varchar(max))
AS
BEGIN
/*
exec GetCommaSepStringsByCommaSepNumericIds '1xx1, 1xx2, 1xx3'
*/
DECLARE @returnCommaSepIds varchar(max);
with cte as (
select distinct Left(qc.myString, 1) + '-' + substring(qc.myString, 2, 9) + '-' + substring(qc.myString, 11, 7) as myString
from q_CoaRequestCompound qc
JOIN
dbo.SplitStringToNumberTable(@CommaSepNumericIds) AS s
ON
qc.q_CoaRequestId = s.ID
where SUBSTRING(upper(myString), 1, 1) in('L', '?')
)
SELECT @returnCommaSepIds = COALESCE(@returnCommaSepIds + ''',''', '''') + CAST(myString AS varchar(2x))
FROM cte;
set @returnCommaSepIds = @returnCommaSepIds + ''''
SELECT @returnCommaSepIds
End
FUNCTION [dbo].[SplitStringToNumberTable]
(
@commaSeparatedList varchar(max)
)
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
