Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tech_1
Posting Yak Master
129 Posts |
 Posted - 2013-04-24 : 09:03:06
|
Hi all.I am passing a comma delimited list into a SPROC which uses the IN clause.I need to split this so the SPROC "works" with it.I will be passing it in this format:'item1, item2, item3'I need to then do a split on the comma in the SPROC.How can I create a split function in SQL Server so I can make this sproc work with the input parameter?quote:
CREATE PROCEDURE [dbo].[GetControlSerialNumberDuplicates] ( @controlNumbers varchar(2000) -- comma delimited for multiple)ASBEGIN SET NOCOUNT ON; SELECT ControlNumber,ControlNextCalDueDate, ControlStatus FROM Control WHERE (ControlNumber IN (@controlNumbers)) AND ControlRecDel = 0END
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-24 : 09:08:02
|
| There are many spslit functions available online. One of the most efficient ones is Jeff Moden's function here: http://www.sqlservercentral.com/articles/Tally+Table/72993/ The function is in Figure 21 of that article. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-24 : 17:26:36
|
quote:
Originally posted by visakh16
alsohttp://visakhm.blogspot.in/2013/01/delimited-string-split-xml-parsing.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Just be careful if you have any of the XML special characters (such as &, < etc.) in your string - this can fail. You can work around those with a little bit of effort. Even if you do that, this would be much less efficient compared to Jeff's code.On the plus side, Visakh's method does not require you to install yet another function on your server. However, there are some functions that I consider well-worth installing - one being Jeff's delimitedSplit8K, and another is Adam Machanic's sp_whoisactive (which of course, is not a string splitter; it is completely something else). |
|
|
|
|
|
|
split function