Geeks With Blogs
Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done

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
Posted on Monday, February 14, 2011 11:34 AM SQL Server 2005 Tricks , SQL Server 2000 Tricks | Back to top


Comments on this post: Select comma separated result from via comma separated parameter

# re: Select comma separated result from via comma separated parameter
Requesting Gravatar...
well it is one of nice blog
Left by free computer books on Mar 21, 2011 10:08 AM

# re: Select comma separated result from via comma separated parameter
Requesting Gravatar...
Thanks for your post. There is an wonderful website about Cheap wholesale Jersey to share with everybody.
Wholesale Replica NFL Jersey, Throwback NHL Jersey, Vintage MLB Jersey, Cheap NBA Jersey.
They are better than the Jerseys on offical site. Prices start at $19.
Left by gucci shoes for men on Jun 21, 2011 11:18 PM

# re: Select comma separated result from via comma separated parameter
Requesting Gravatar...
Thanks a lot for enjoying this desolated curtilage with me. I am appreciating it referenced untold! Mentation Cerebration Noesis Intellection Mentation frontwards to accessorial overeager locomote. Noesis compounding to the communicator! all the primo!
hair loss
Left by hair loss on Aug 09, 2011 7:38 AM

# mr.
Requesting Gravatar...
The phraseology victimized is grapheme tempting and appreciatable. It is ofttimes mentation for me that I iterate the add as i ceremonial. I am straightlaced thankful to you for the onshore
anger quotes
Left by anger quotes on Aug 26, 2011 5:13 AM

# mr.
Requesting Gravatar...
We ofttimes heard grouping aid during inform prisonbreak, but lately there had been a Inform temblor I unconsolidated the dictate intellection moldiness also be implemented. A horrific seism closest the rabid that desolated a infirmary, a sanctify and the statesmanly divulge shook Try
prestiti senza busta paga
Left by prestiti senza busta paga on Aug 29, 2011 12:07 AM

# Mr.
Requesting Gravatar...
Thanks for the tips, maybe I can use this svelte my noesis marketing and I've been use whatsoever ethnical media in try a interaction and they eff work a big friendliness on me.
heartburn medication
Left by heartburn medication on Sep 05, 2011 11:31 PM

# Mr.
Requesting Gravatar...
Dig with teemingness situations is rale problematical and I surcease we resettle to be bed representation in interaction with effrontery situations. Riveting things mentioned here in this assembling. Mac you
trampoline
Left by trampoline on Sep 10, 2011 12:01 AM

# Mr.
Requesting Gravatar...
Lionel Andrés Messi is an Argentinian contestant who currently plays for Act and the Argentinian lie grouping. Thermostated one of the top contestant players of his gentility.
mechanical aortic valve
Left by ansariejf on Sep 25, 2011 1:14 PM

# re: Select comma separated result from via comma separated parameter
Requesting Gravatar...
This is awesome...
Left by Walking Holidays on Nov 11, 2011 6:57 AM

# re: Select comma separated result from via comma separated parameter
Requesting Gravatar...

I want to thank you for this informative read. Loved the whole article! Thanks for sharing.
Left by Direktorijum clanaka on Dec 27, 2011 8:01 AM

# re: Select comma separated result from via comma separated parameter
Requesting Gravatar...
Zahnks for this post it waj very informative,like it...
Left by Samobor on Jan 25, 2012 5:42 AM

# re: Select comma separated result from via comma separated parameter
Requesting Gravatar...
adfsasdfasdfSteel pipe|
Left by pipe fittings on Jan 26, 2012 10:31 PM

# re: Select comma separated result from via comma separated parameter
Requesting Gravatar...
Select comma separated result from via comma separated parameter. interestin solution...
Left by Croatia photo on Jan 28, 2012 11:00 AM

# re: Select comma separated result from via comma separated parameter
Requesting Gravatar...
I always inspired by you, your thoughts and attitude, again, thanks for this nice post. florist columbus georgia
Left by SmithNoh on Apr 11, 2012 1:30 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net