July 22, 2009 at 6:52 pm
Hi,
I am trying to help one of my managers build a department-level search from our database.
She wants a web developer in our department to pass a list of IDs to a stored procedure (I've already written) and have my procedure parse the IDs (as a single string) and build a list of ID strings to place inside a variable used with an IN keyword. It looks a little like this:
DECLARE @ListOfIDs varchar(300)
...
parsing...
...
WHERE ID IN (@ListOfIDs)
When the code runs the @ListOfIDs variable looks like 'kyle', 'sam', matthew' (when there's more than one ID).
The procedure parses the giant string okay. My problem occurs when I fill the @ListOfIDs variable with the IDs that I've parsed the procedure returns an empty record set. When I put just one ID into the @ListOfIDs variable, I get the right data.
How do I get the procedure to recognize multiple names in the @ListOfIDs variable?
Any suggestions would be greatly appreciated. And yes I understand that parsing strings in t-sql is not the best of ideas.
Thanks for any help/tips you can offer,
Duck :w00t:
July 22, 2009 at 7:59 pm
this is a very common requirement; what you want to do is search the scripts section for the "split" function, there's several version out there, all can do what you need;
you use the split function to chop your delimited list into a table variable.
then your query looks like this:
DECLARE @ListOfIDs varchar(300)
...
parsing...
...
WHERE ID IN dbo.Split(@ListOfIDs,',') --the delimiter is a parameter of the function;this one is a comma
Lowell
July 22, 2009 at 8:55 pm
Lowell, thank you very much for the pointer. I sometimes don't think of the other functions that are available inside of SQL Server.
I appreciate your help and will give it a try!
Thank you very much.
Duck 🙂
July 22, 2009 at 9:48 pm
There are a whole lot of split functions... with extremely rare exception, if it uses a While Loop, it's slow. Don't even look at one that uses an XML split or recurrsion of any kind...
Please take a look at the following article... converting such a thing to an inline table valued function will make it lightning quick.
http://www.sqlservercentral.com/articles/T-SQL/63003/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2009 at 8:48 am
I am really tired because my 1 yr. old woke me really early this morning.
I'm looking through the suggested solutions to this problem, and I'm not understanding them. I'm looking at SPLIT functions that return an entire table. I'm also looking at the Talley Table idea of Jeff's, which also inserts information into a table.
How does this help me take a sting with commas (',') separating values, and put the string(s) correctly into my stored procedure variable? :crazy:
DECLARE @ListOfIDs varchar(300)
...
...
WHERE ID IN (@ListOfIDs)
Where the list of IDs look like: 'kyle, scott, luke' and I've parsed it so the @ListOfIDs looks like: 'kyle', 'scott', 'luke'. I'm not seeing how interacting with a table for a split/talley table will help me return a record set from my stored procedure when I have a string I'm already stuffing into it and getting nothing.
I'm not understanding/seeing how this is supposed to help my break my string apart.
July 23, 2009 at 8:57 am
Show us your procedure and well show you how it works.
July 23, 2009 at 9:07 am
here is a specific example with a specific version of a Split() function:
note it returns a table.
Declare @MyString varchar(max)
Set @MyString = 'Amanda,Devin,Sydnee,Valery,Gloria,Cecelia,Caroline,Estevan'
select * from dbo.split(@MyString,',')
--another example
select * from firstnames where firstname in (SELECT strval From dbo.split(@MyString,','))
--results
strval
-------
Amanda
Devin
Sydnee
Valery
Gloria
Cecelia
Caroline
Estevan
once you see that it returns a table, you can see it's possibilities a bit better.
here is the function i used:
CREATE FUNCTION [dbo].[SPLIT] (
@str_inVARCHAR(max),
@separatorVARCHAR(4) )
RETURNS @strtable TABLE (strvalVARCHAR(max))
AS
BEGIN
DECLARE
@OccurrencesINT,
@CounterINT,
@tmpStrVARCHAR(max)
SET @Counter = 0
IF SUBSTRING(@str_in,LEN(@str_in),1) @separator
SET @str_in = @str_in + @separator
SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)
SET @tmpStr= @str_in
WHILE @Counter <= @Occurrences
BEGIN
SET @Counter = @Counter + 1
INSERT INTO @strtable
VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))
SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)
IF DATALENGTH(@tmpStr) = 0
BREAK
END
RETURN
END
Lowell
July 23, 2009 at 9:46 am
Here is a delimited split function that does not use a while loop:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[DelimitedSplit] (
@pString varchar(max),
@pDelimiter char(1)
)
returns table
as
return
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
a4 as (select
1 as N
from
a3 as a
cross join a2 as b),
Tally as (select top (len(@pString))
row_number() over (order by N) as N
from
a4),
ItemSplit(
ItemOrder,
Item
) as (
SELECT
N,
SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)
FROM
Tally
WHERE
N < LEN(@pDelimiter + @pString + @pDelimiter)
AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = @pDelimiter --Notice how we find the delimiter
)
select
row_number() over (order by ItemOrder) as ItemID,
Item
from
ItemSplit
July 23, 2009 at 10:58 am
Thanks everyone for your input and help. It was beneficial to getting this to work. I think I have it now and I am grateful.
Lowell, if you are still around I do have a couple of quick closing questions.
In your first response, was I reading your code too literally? I was expecting the string splitting funcition to put strings into the IN keyword. Your second set of code showed that you would query the table variable that was built in the string splitter. Was this my mistake?
Second, when an issue is solved, how do I mark the issue as closed/solved?
Again, thanks for all your help. I appreciate it.
July 23, 2009 at 11:07 am
head_contusion (7/23/2009)
Second, when an issue is solved, how do I mark the issue as closed/solved?
You don't here on SSC. Many in this community believe that there is much more to be gained by the discussion that follows, sometimes after the question/problem/issue has been resolved.
July 23, 2009 at 11:33 am
head_contusion (7/23/2009)
In your first response, was I reading your code too literally? I was expecting the string splitting funcition to put strings into the IN keyword. Your second set of code showed that you would query the table variable that was built in the string splitter. Was this my mistake?
yep my fault... i was trying to present the idea, and since it was still pseudo code and not copy and pastable examples. the first example i pasted was not correct;
the later example i through out was tried and tested code, and also presented the correct way to include it in your procedure.
sorry about the confusion.
Lowell
July 23, 2009 at 12:45 pm
head_contusion (7/23/2009)
I am really tired because my 1 yr. old woke me really early this morning.I'm looking through the suggested solutions to this problem, and I'm not understanding them. I'm looking at SPLIT functions that return an entire table. I'm also looking at the Talley Table idea of Jeff's, which also inserts information into a table.
How does this help me take a sting with commas (',') separating values, and put the string(s) correctly into my stored procedure variable? :crazy:
DECLARE @ListOfIDs varchar(300)
...
...
WHERE ID IN (@ListOfIDs)
Where the list of IDs look like: 'kyle, scott, luke' and I've parsed it so the @ListOfIDs looks like: 'kyle', 'scott', 'luke'. I'm not seeing how interacting with a table for a split/talley table will help me return a record set from my stored procedure when I have a string I'm already stuffing into it and getting nothing.
I'm not understanding/seeing how this is supposed to help my break my string apart.
Sorry... bad on all our parts. You don't use the result in an "IN"... you join to it as if it were a table.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2009 at 1:53 pm
Lynn Pettis (7/23/2009)
head_contusion (7/23/2009)
Second, when an issue is solved, how do I mark the issue as closed/solved?You don't here on SSC. Many in this community believe that there is much more to be gained by the discussion that follows, sometimes after the question/problem/issue has been resolved.
Especially if we feel like digressing into a discussion of the best split functions, EXISTS vs. IN, or stored procedures vs. client side code…
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply