charlist_to_table for mvp function

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
ENDI 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" <Lisa@.discussions.microsoft.com> wrote in message
news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> 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|||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
"Bruce L-C [MVP]" wrote:
> 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" <Lisa@.discussions.microsoft.com> wrote in message
> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> > 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
>
>|||I meant this above
select * from charlist_to_table(@.codes,default)
"Lisa" wrote:
> 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
> "Bruce L-C [MVP]" wrote:
> > 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" <Lisa@.discussions.microsoft.com> wrote in message
> > news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> > > 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
> >
> >
> >|||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" <Lisa@.discussions.microsoft.com> wrote in message
news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> 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
> "Bruce L-C [MVP]" wrote:
>> 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" <Lisa@.discussions.microsoft.com> wrote in message
>> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
>> > 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
>>|||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.
"Bruce L-C [MVP]" wrote:
> 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" <Lisa@.discussions.microsoft.com> wrote in message
> news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> > 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
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> 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" <Lisa@.discussions.microsoft.com> wrote in message
> >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> >> > 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
> >>
> >>
> >>
>
>|||never mind. It actually worked when I ran within the sp in ssrs. thanks.
Before I was testing it in query analyzer.
"Lisa" wrote:
> 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.
> "Bruce L-C [MVP]" wrote:
> > 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" <Lisa@.discussions.microsoft.com> wrote in message
> > news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> > > 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
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> 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" <Lisa@.discussions.microsoft.com> wrote in message
> > >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> > >> > 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
> > >>
> > >>
> > >>
> >
> >
> >|||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" <Lisa@.discussions.microsoft.com> wrote in message
news:FEB9528A-30AB-44DC-A9FD-DBA43412B2CD@.microsoft.com...
> 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.
> "Bruce L-C [MVP]" wrote:
>> 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" <Lisa@.discussions.microsoft.com> wrote in message
>> news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
>> > 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
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> 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" <Lisa@.discussions.microsoft.com> wrote in message
>> >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
>> >> > 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
>> >>
>> >>
>> >>
>>|||I bet it was the issue with the carriage return.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:0B5C65E7-8118-49B2-A4D7-A30ACCD372D5@.microsoft.com...
> never mind. It actually worked when I ran within the sp in ssrs. thanks.
> Before I was testing it in query analyzer.
> "Lisa" wrote:
>> 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.
>> "Bruce L-C [MVP]" wrote:
>> > 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" <Lisa@.discussions.microsoft.com> wrote in message
>> > news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
>> > > 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
>> > >
>> > > "Bruce L-C [MVP]" wrote:
>> > >
>> > >> 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" <Lisa@.discussions.microsoft.com> wrote in message
>> > >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
>> > >> > 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
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >|||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
"Bruce L-C [MVP]" wrote:
> 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" <Lisa@.discussions.microsoft.com> wrote in message
> news:FEB9528A-30AB-44DC-A9FD-DBA43412B2CD@.microsoft.com...
> > 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.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> 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" <Lisa@.discussions.microsoft.com> wrote in message
> >> news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> >> > 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
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> 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" <Lisa@.discussions.microsoft.com> wrote in message
> >> >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>