Passing comma-delimited list as a parameter

Question

Post reply

Passing comma-delimited list as a parameter

Ray K

SSC-Dedicated

Points: 31163
More actions
February 24, 2012 at 1:17 pm

#391648
We all know about searching comma delimited lists, like this:
select * from People where Name in ('Larry','Curly','Moe')
Here's my situation: I have a stored procedure that makes use of a dynamic query and parameters. I'd like to retrofit my SP to accommodate comma-delimited lists like the example above.
I have two questions that are both related to this.
- (1) How do I pass a list of strings? For example:
  declare @Names varchar(50) = 'Larry, Curly, Moe'
  declare @sql nvarchar(1024)
  declare @params nvarchar(512) = N'@NameList varchar(50)'
  @sql = N'select * from People where Name in (@NameList)'
  exec sp_executesql @sql, @params, @NameList = @Names
  I tried messing around with different combinations of single-quotes, but haven't been able to make it work.
- (2) How do I pass a delimited parameter when the search column is a different data type? For example:
  declare @IDs varchar(50) = '1, 2, 3'
  declare @sql nvarchar(1024)
  declare @params nvarchar(512) = N'@IDList varchar(50)'
  @sql = N'select * from People where ID in (@IDList)'
  -- ID is type int
  exec sp_executesql @sql, @params, @IDList = @ID
  I tried doing a CAST on ID, but that doesn't seem to work.
Thoughts, anyone?
As always, thanks for your help!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Take a look at the string splitter link in my signature. It is exactly what you need.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ray K SSC-Dedicated Points: 31163 More actions · Answer 2

Sean Lange (2/24/2012)
Take a look at the string splitter link in my signature. It is exactly what you need.

Ahhh, Jeff Moden comes to the rescue again! 🙂

I need to take some time to digest his article. When (if?) I can get my code working, I'll post again!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Post back if you have problems. I suspect when you dig through his article you will see how to utilize this against your proc (hint cross apply the function). 😉

If you get stuck post back and somebody will dive in and help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ray K SSC-Dedicated Points: 31163 More actions · Answer 4

Good article! I especially liked his comment about writing a full retraction to his anti-RBAR ministry! And I'm tempted to run home and make up a batch of beer popsicles! 😀

The splitter code does look interesting, although I might hold off on trying it out when I have some more brainpower and it's not past 4 PM on a Friday like it is here!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Do be careful or he will be forced to come after you with his pork chop launcher. :w00t:

Have a great weekend.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dwain Camps SSC Guru Points: 86948 More actions · Answer 6

I had a similar problem once and used a string splitter on the list to get what I needed out of it. I happened to choose a SplitString that was based on a recursive CTE (many links to this) and its recursion limit meant I could only include 100 elements in the list. Yes I know that can be increased.

However I believe that there is a better method, which is putting the list of values into an XML document and parsing that document to get the list. There are two ways to pass it in - either as a document or as a handle to that document.

Any time I have an SP that needs to pass in a list of values now, I offer all 3 of these options. I've found the handle to the document to be the most efficient. I still use the delimited string option when testing though as it is convenient.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

dwain.c (2/24/2012)
I had a similar problem once and used a string splitter on the list to get what I needed out of it. I happened to choose a SplitString that was based on a recursive CTE (many links to this) and its recursion limit meant I could only include 100 elements in the list. Yes I know that can be increased.
However I believe that there is a better method, which is putting the list of values into an XML document and parsing that document to get the list. There are two ways to pass it in - either as a document or as a handle to that document.
Any time I have an SP that needs to pass in a list of values now, I offer all 3 of these options. I've found the handle to the document to be the most efficient. I still use the delimited string option when testing though as it is convenient.

Read the article that Sean provided the link for. You'll see why using XML isn't the way to go here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

Ray K (2/24/2012)
Good article! I especially liked his comment about writing a full retraction to his anti-RBAR ministry! And I'm tempted to run home and make up a batch of beer popsicles! 😀
The splitter code does look interesting, although I might hold off on trying it out when I have some more brainpower and it's not past 4 PM on a Friday like it is here!

When you have sufficiently suffoncified your suffinciency, here's another article on passing delimited parameters that would make good use of the newer DelimitedSplit8K splitter.

http://www.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 9

Dang, Joe. That's the 3rd one I've seen today... Informative, helpful, kind, and courteous. It's a really nice change. Keep it up! The tone of your post is freakin' awesome! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)