Select comma separated result from via comma separated parameter
Posted
by Rodney Vinyard
on Geeks with Blogs
See other posts from Geeks with Blogs
or by Rodney Vinyard
Published on Mon, 14 Feb 2011 17:34:37 GMT
Indexed on
2011/02/14
23:26 UTC
Read the original article
Hit count: 371
Filed under:
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
© Geeks with Blogs or respective owner
