Geeks With Blogs

News


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

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

 

 

 

 

 

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

Posted on Friday, May 28, 2010 1:56 PM SQL Server 2005 Tricks , SQL Server 2000 Tricks | Back to top


Comments on this post: Better than dynamic SQL - How to pass a list of comma separated IDs into a stored proc

# re: Better than dynamic SQL - How to pass a list of comma separated IDs into a stored proc
Requesting Gravatar...
[url=http://www.aquaconnect.net]Mac Remote Desktop[/url]
I absolutely admire the author for attributing their time for this astounding article. Surely many readers can benefit on this topic. Thank you.
Left by Mac Remote Desktop on Nov 10, 2010 1:36 AM

# re: Better than dynamic SQL - How to pass a list of comma separated IDs into a stored proc
Requesting Gravatar...
The article is truly the uppermost on this worthy concept. I really admire you for the astonishing lucidity in your characteristic writing. I suit in with your conclusions and will thirstily look forward to your upcoming updates.
Left by Canada Pharmacy Scam on Jan 13, 2011 3:15 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net