April 8, 2008 at 10:13 am
Hi all. Hope someone has a sensible solution for me...
What I want to accomplish is to execute a procedure that takes a list of names as a delimited list and uses it as a where in clause.
I think my example explains itself.
create table #mytbl( col1 int , col2 varchar(10))
go
insert #mytbl values (1,'a')
insert #mytbl values (2,'a')
insert #mytbl values (3,'b')
insert #mytbl values (4,'b')
insert #mytbl values (5,'c')
go
create procedure test1 @list varchar(80)=null
as
begin
if @Excludelist is null
begin
select * from #mytbl
end
else begin
select * from #mytbl where col2 in (@list)
end
end
Okey now we have our demo data.
When you execute the procedure like so
exec test1 'a'
you will get a result with 1,2 and col2 as a
Then i wanted to get a and b and come up short ,
Executed the procedure with
exec test1 'a,b'
exec test1 '''a'',''b'''
but unable to get a result using more than one name as in clause.
Has anyone a solution on how this could be accomplished without using an CLR stored procedure ?
kgunnarsson
Mcitp Database Developer.
April 8, 2008 at 10:21 am
Interesting how you can find a solution just 2 min after you ask for help 🙂
It's not the best solution but it works.
create procedure test1 @list varchar(80)=null
as
begin
declare @sql varchar(max)
if @list is null
begin
set @sql = 'select * from #mytbl'
exec(@sql)
end
else begin
set @sql = 'select * from #mytbl where col2 in ('+ @list+ ')'
exec(@sql)
end
end
exec test1 '''a'',''b'''
if anyone has an idea how i can do this with out constructing the sql and executing it , it would be appreciated.
kgunnarsson
Mcitp Database Developer.
April 8, 2008 at 10:26 am
this quick and dirty function works well for text lists.
create function [dbo].[fListToItems]( @list varchar(max), @delim varchar(max))
returns @returnTable table
( item varchar(255) not null )
as begin
declare @xml XML
set @xml = ' '
insert into @returnTable
SELECT data.item.value('.','varchar(255)')
FROM @xml.nodes('//item') as data(item)
return
end
the result is a table.
select * from dbo.fListToItems( 'a,b,c', ',' )
item
----
a
b
c
select ...
from #mytable as M join dbo.fListToItems( @list, ',' ) as X
on X.item = M.col2
April 8, 2008 at 10:33 am
Antonio... that's some interesting code, but
select * from dbo.fListToText( 'a,b,c', ',' )
the function flisttotext is missing from the code.
At first I thought it was using flisttoitems, but when I changed the select to use that I get no records back.
select * from dbo.fListToItems( 'a,b,c', ',' )
This returns no records, so the join doesn't work.
April 8, 2008 at 11:01 am
Avoid the dynamic SQL and use an appropriate parsing function if you have to do this.
A better approach is not to pass delimited lists around. If you are designing something new, best practice would be to use a table or temp table to pass in the list or use an XML string rather than a delimited string.
There are dozens of articles on this site alone relating to this. Do some searching and I am sure you will find some great options.
If Steve is reading this thread he may be able to direct you to a good one posted recently.
April 8, 2008 at 11:05 am
Brilliant , thax ... and yes i am doing new stuff, so i can alter my initial design.
kgunnarsson
Mcitp Database Developer.
April 8, 2008 at 11:08 am
:mtassin
sorry about the typo. see revised post.
April 8, 2008 at 11:09 am
There is an article right in the daily newsletter for today that is close to what you are referring to:
http://www.sommarskog.se/share_data.html#XML
This outlines some techniques for transferring table data between stored procedures.
April 8, 2008 at 12:02 pm
Actually... the joys of the Tally Table return... 🙂
http://www.sqlservercentral.com/Forums/Topic452198-338-1.aspx
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM MASTER.dbo.SysColumns sc1, MASTER.dbo.SysColumns sc2
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
CREATE FUNCTION [dbo].[StringParser]
(@String_in varchar(max),
@Delimiter_in char(1))
returns table
as
return (
SELECT top 100 percent
SUBSTRING(@String_in+@Delimiter_in, N,
CHARINDEX(@Delimiter_in, @String_in+@Delimiter_in, N) - N) as Parsed
FROM dbo.Tally
WHERE N <= LEN(@String_in)
AND SUBSTRING(@Delimiter_in + @String_in, N, 1) = @Delimiter_in
ORDER BY N
);
create table #mytbl( col1 int , col2 varchar(10))
go
insert #mytbl values (1,'a')
insert #mytbl values (2,'a')
insert #mytbl values (3,'b')
insert #mytbl values (4,'b')
insert #mytbl values (5,'c')
go
create procedure test1 @list varchar(80)=null
as
begin
if @Excludelist is null
begin
select * from #mytbl
end
else begin
select a.* from #mytbl a join dbo.Stringparser(@list) b on
a.col2 = b.parsed
end
end
Never mind... it works fine as is up there... my test copy had a typo I corrected when I posted it. :hehe:
April 8, 2008 at 12:22 pm
I believe I remember Antonio and Jeff doing some performance testing on parser functions and if I recall correctly the tally table is slightly faster (maybe a ms or 2). On small datasets the resutls were pretty much in a dead heat.
I still use the xml method to parse because it is blazing, does not require a perminent table, and is easy to type.
April 8, 2008 at 12:25 pm
Adam Haines (4/8/2008)
I believe I remember Antonio and Jeff doing some performance testing on parser functions and if I recall correctly the tally table is slightly faster (maybe a ms or 2). On small datasets the resutls were pretty much in a dead heat.I still use the xml method to parse because it is blazing, does not require a perminent table, and is easy to type.
The xml returns blank to me when I run
select * from dbo.fListToItems( 'a,b,c', ',' )
April 8, 2008 at 5:30 pm
April 8, 2008 at 6:40 pm
the 'typo' stems from embedded XML tags in the post. when the browser encounters a tag it doesn't recognize (in this case <item> ) it just ignores it. pita!
ALTER function [dbo].[fListToItems]( @list varchar(max), @delim varchar(max))
returns @returnTable table
( item varchar(255) not null )
as begin
declare @xml XML
set @xml = '<item>' + REPLACE(@list, isnull(@delim,','), '</item><item>') + '</item>'
insert into @returnTable
SELECT data.item.value('.','varchar(255)')
FROM @xml.nodes('//item') as data(item)
return
end
April 8, 2008 at 7:10 pm
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply