charlist_to_table for mvp function

[Lisa]

Hi, I found the following function on this site and am trying to use it my
reports.
The dataset for my mvp is different from my stored proc I'm using.
The data set for my mvp is simple

codes dataset =
select distinct codes from tbl_codes

values are
AAA-2222
BBB-3333
CCC-444

In my stored procedure I call the function

select * from dbo.tbl_codes as a
where (a.codes in(select nstr from charlist_to_table(@codes,',')))

the issue is that it only retrives the first code instead of all three.

this is how I test it:
select nstr from charlist_to_table
('AAA-2222,
BBB-3333,
CCC-444
',',')

I get the following
AAA-2222,
BBB-3333,
CCC-444

I don't think the function is working in the sp because there is a space in
front of the values. Even when I put a space in the before the codes data
set I still only get the data for the first code AAA-2222.

Am I missing something in the code below. Thanks, Lisa

CREATE FUNCTION [dbo].[charlist_to_table]
(@list ntext, @delimiter nchar(1) = N',')

RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))
RETURN
END

[Bruce L-C [MVP]]

I call it using default keyword.

select str from charlist_to_talbe(@codes,default)

I use a join.

select a.* from dbo.tbl_codes a inner join charlist_to_table(@CODES,Default)
b on a.codes = b.str

change b.str to b.nstr depending on the datatype of a.codes.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Lisa" <Li...@discussions.microsoft.com> wrote in message
news:21F23497-06A6-4BF0...@microsoft.com...

[Lisa]

Thanks for your help. I understand, but there is still something missing.

see the test
declare @codes varchar(50)

select
@codes = ('SWA35-2948,
SWAP2-2892,
SWA27-2946,
GRE1-2936,
ADM2-2930,
SWA28-2938,
SWUA2-2938,
SWA31-2948,
SWAP4-2950,
SWUA3-2938)
--test
print @codes
this come out correct
SWA35-2948,
SWAP2-2892,
SWA27-2946,
GRE1-2936,
ADM2-2930,
SWA28-2938,
SWUA2-2938,
SWA31-2948,
SWAP4-2950,
SWUA3-2938

but when I run this
select * from charlist_to_table(@promo_code,default)
I get the following

listpos str nstr
1 SWA35-2948 SWA35-2948
2 SWAP2-2892 SWAP2-2892
3 SWA27-2946 SWA27-2946
4 GRE1-2936 GRE1-2936
5

I should have 10 listpos and there still spaces in front out the other values.
so this only returns the first row's value for code SWA35-2948
select a.* from dbo.swp_camps as a
inner join dbo.charlist_to_table(@codes,Default) as b on a.codes = b.nstr

Any suggestions. Thanks, Lisa

[Lisa]

I meant this above
select * from charlist_to_table(@codes,default)

[Bruce L-C [MVP]]

Make your @codes larger. At least for the below that is why it is not
working.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Lisa" <Li...@discussions.microsoft.com> wrote in message

news:53AD8EAF-4D39-4C63...@microsoft.com...

[Lisa]

thanks, that worked. But, I still have the space issue

listpos str nstr
1 SWA35-2948 SWA35-2948
2 SWAP2-2892 SWAP2-2892
3 SWA27-2946 SWA27-2946

in the str and nstr fields all but the first row has spaces in front of the
value. This is why it's only returning the first row. thanks for you help.

[Lisa]

never mind. It actually worked when I ran within the sp in ssrs. thanks.
Before I was testing it in query analyzer.

[Bruce L-C [MVP]]

Are you putting it on separate lines when you do your test?

select nstr from charlist_to_table
('AAA-2222,
BBB-3333,
CCC-444
',',')

Since you are enclosing the whole thing in a string it is included the
carriage return (which will look like a blank). Do it like this:

select nstr from charlist_to_table
('AAA-2222,BBB-3333,CCC-444',',')

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Lisa" <Li...@discussions.microsoft.com> wrote in message

news:FEB9528A-30AB-44DC...@microsoft.com...

[Bruce L-C [MVP]]

I bet it was the issue with the carriage return.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Lisa" <Li...@discussions.microsoft.com> wrote in message

news:0B5C65E7-8118-49B2...@microsoft.com...

[Lisa]

that was it.

It actually makes sense now because in SSRS the MVP is
('AAA-2222,BBB-3333,CCC-444')

I'm just use to writing it like this in sql
('AAA-2222,
BBB-3333,
CCC-444')

Thanks - Lisa

[Yogesh]

Hi ,

This post is really very useful, we used it , it is working fine.

But,Since we are moving our Db from Sql server to Oracle.
Could any one provide me the code to create this Function 'charlist_to_table' In Oracle Pl/Sql Form...

Thanks in advance
Yogesh
(yo_...@yahoo.co.in)

From http://www.developmentnow.com/g/115_2006_4_0_0_741471/charlist-to-table-for-mvp-function.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/