Check image in a SQL Server DB

Hello!

I want to read an image from the database. I've this function but she returns me an error that suggest me that the image might be damaged or corrupted.

I would like to know if it's possible and how to check an image in the DB.

Thank you!

i use the datalenght function to check the size of the image in the DB, it returned "1" in some cases and "null".

I assumed that the imagens aren't ok, and i'll see if i can find the original ones to upload them to the BD.

Am i correct? Can i assume that the number one (1), returned from the function "datalength" means that the images have 1byte of size which i know it's false?

Thank you

Check if exist

Hi guys help please..is there a function in MS SQL that check if a particular value exist in a row and would return a boolean value base from what found, Return True if it found something and False if it does not found one. I've try the EXISTS function but I cant get the rigth syntax..Any help will be greatly appreciated!

OR Maybe you can help me directly with my problem. I want to check first in my Table 1 with 3 columns if value X exists in column 1 and if X exists UPDATE that column with value Y and if value X does not exists INSERT something in the Table 1. Any suggestion or Comments will be greatly appreciated!Hi

Post what you've got for your exists syntax. It should merely require some tweaking.|||Here it is.
EXISTS(select Sales_Date from CFREE_Sales where Sales_Date = '8/31/2007 12:00:00 AM')|||Try:
IF EXISTS(select Sales_Date from CFREE_Sales where Sales_Date = '20070831') BEGIN
PRINT 'It exists'
END
ELSE BEGIN
PRINT 'It does not exist'
END What is the result|||Yah...Thats what I need..Thanks a lot!|||An alternative to if exists (select * from #t1 where c1='x') begin
update #t1 set c2=c2+100 where c1='x'
end
else begin
insert into #t1 values ('x',100)
endisupdate #t1 set c2=c2+100 where c1='x'
if @.@.rowcount=0 begin
insert into #t1 values ('x',100)
end|||update #t1 set c2=c2+100 where c1='x'
if @.@.rowcount=0 begin
insert into #t1 values ('x',100)
endbingo! :beer:

Check if a Table is used in any sp or function

Hello, there,

I am trying to find out if a table is used by any of the stored
procedures or functions.

I can generate all the scripts and look for it. But is there an easy
way?

THX

JohnQuery the syscomments table looking for the name of the table.

Q. John Chen wrote:

Quote:

Originally Posted by

Hello, there,
>
>
I am trying to find out if a table is used by any of the stored
procedures or functions.
>
I can generate all the scripts and look for it. But is there an easy
way?
>
THX
>
John

|||Q. John Chen (qjchen@.email.com) writes:

Quote:

Originally Posted by

I am trying to find out if a table is used by any of the stored
procedures or functions.
>
I can generate all the scripts and look for it. But is there an easy
way?

sp_depends.

However, it's not reliable, since dependencies are lost if the table
is dropped and recreated. Or the proc/function was created before the
table was.

syscomments that Stu mentions is neither that safe, as text here is
sliced into chunks of 4000 chars, and the table name could occur
on a chunk border.

So scripting is the only way. Or searching the version-control system.
Because you do keep all your code under version control. don't you?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Q. John Chen schreef:

Quote:

Originally Posted by

Hello, there,
>
>
I am trying to find out if a table is used by any of the stored
procedures or functions.
>
I can generate all the scripts and look for it. But is there an easy
way?
>
THX
>
John

Yep, there is:

CREATE proc [FindInObjects] (@.Search varchar(300))
as

SELECT so.xtype, so.name, sc.TEXT
FROM dbo.syscomments sc RIGHT OUTER JOIN
dbo.sysobjects so ON sc.id = so.id
WHERE so.xtype IN ('P', 'V', 'FN', 'TR') AND (sc.TEXT LIKE '%' +
@.Search + '%')

This will show you a list of all the objects that contain the search
string.

Enjoy,

GJ|||(gjvdkamp@.gmail.com) writes:

Quote:

Originally Posted by

CREATE proc [FindInObjects] (@.Search varchar(300))
as
>
SELECT so.xtype, so.name, sc.TEXT
FROM dbo.syscomments sc RIGHT OUTER JOIN
dbo.sysobjects so ON sc.id = so.id
WHERE so.xtype IN ('P', 'V', 'FN', 'TR') AND (sc.TEXT LIKE '%' +
@.Search + '%')
>
This will show you a list of all the objects that contain the search
string.

...unless the procedure name in question has been split up over a
chunk border. Keep in mind that the procedure text in syscomments is
split up in chunks of 4000 characters, and the split can well be in
the middle of an identifier.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Check Disk Size in SQL 2000

I was wondering if there is a way to check the total disk size using
SQL 2000. There is a function to check how much is left, but havnt
found anything to check total size.Hi
I am not sure what you mean by this! You can look at the output of sp_helpdb
or sp_helpfile to get the file sizes of the databases and you can do this for
each database using a cursor! There are also SQL Server related permon
counters to monitor file sizes as well as perfmon counters to monitor disc
space usage. You can set an alert using perfmon if you disc usage reaches a
threshold. Alternatively you could use MOM to monitor your systems.
HTH
John
"cpatel1@.gmail.com" wrote:
> I was wondering if there is a way to check the total disk size using
> SQL 2000. There is a function to check how much is left, but havnt
> found anything to check total size.
>

Check Disk Size in SQL 2000

I was wondering if there is a way to check the total disk size using
SQL 2000. There is a function to check how much is left, but havnt
found anything to check total size.Hi
I am not sure what you mean by this! You can look at the output of sp_helpdb
or sp_helpfile to get the file sizes of the databases and you can do this fo
r
each database using a cursor! There are also SQL Server related permon
counters to monitor file sizes as well as perfmon counters to monitor disc
space usage. You can set an alert using perfmon if you disc usage reaches a
threshold. Alternatively you could use MOM to monitor your systems.
HTH
John
"cpatel1@.gmail.com" wrote:

> I was wondering if there is a way to check the total disk size using
> SQL 2000. There is a function to check how much is left, but havnt
> found anything to check total size.
>

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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

CHARINDEX is not working

i am using the below code but its not working .. what could be the reason?
CHARINDEX(@.SPName,@.DateAndIntCols)
to CHARINDEX function i am passing to variables, cant i pass variables to
CHARINDEX?Please elaborate on "not working" . Are there errors? Don't you get expected
results? Don't you get any results? What?
ML
http://milambda.blogspot.com/|||On Thu, 1 Dec 2005 15:33:02 -0800, KL wrote:

>i am using the below code but its not working .. what could be the reason?
>CHARINDEX(@.SPName,@.DateAndIntCols)
>to CHARINDEX function i am passing to variables, cant i pass variables to
>CHARINDEX?
Hi KL,
The following code returns 4 on my computer, which is as expected. What
are the values of @.SPName and @.DateAndIntCols in your case, and what was
the result you got?
DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
SET @.SPName = 'xyz'
SET @.DateAndIntCols = 'uvwxyzabcdef'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I am passing the below values to the variables but its still returning 0...
SET @.SPName ='eUpdated'
SET @.DateAndIntCols = 'eUpdated,eDeleted'
but its working (returning 1) when use the following one...
SET @.DateAndIntCols = 'eUpdated,eDeleted'
CHARINDEX('eUpdated',@.DateAndIntCols)
"ML" wrote:

> Please elaborate on "not working" . Are there errors? Don't you get expect
ed
> results? Don't you get any results? What?
>
> ML
> --
> http://milambda.blogspot.com/|||On Thu, 1 Dec 2005 16:03:03 -0800, KL wrote:

>I am passing the below values to the variables but its still returning 0...
>SET @.SPName ='eUpdated'
>SET @.DateAndIntCols = 'eUpdated,eDeleted'
>but its working (returning 1) when use the following one...
>SET @.DateAndIntCols = 'eUpdated,eDeleted'
>CHARINDEX('eUpdated',@.DateAndIntCols)
Hi KL,
Works for me:
DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
SET @.SPName ='eUpdated'
SET @.DateAndIntCols = 'eUpdated,eDeleted'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
Can you please post your complete code?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is the code i am using ....
DECLARE
@.SPName varchar(128) ,
@.DateAndIntCols varchar(1000)
SELECT @.SPName='eUpdated'
SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
And when i printed the variable @.DateAndIntCols its showing as
'eUpdated,eDeleted'....
"Hugo Kornelis" wrote:

> On Thu, 1 Dec 2005 16:03:03 -0800, KL wrote:
>
> Hi KL,
> Works for me:
> DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
> SET @.SPName ='eUpdated'
> SET @.DateAndIntCols = 'eUpdated,eDeleted'
> SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
>
> Can you please post your complete code?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Here is the complete code i am using ....
DECLARE
@.SPName varchar(128) ,
@.DateAndIntCols varchar(1000)
SELECT @.SPName='eUpdated'
SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
And when i printed the variable @.DateAndIntCols its showing as
'eUpdated,eDeleted'....
"Hugo Kornelis" wrote:

> On Thu, 1 Dec 2005 16:03:03 -0800, KL wrote:
>
> Hi KL,
> Works for me:
> DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
> SET @.SPName ='eUpdated'
> SET @.DateAndIntCols = 'eUpdated,eDeleted'
> SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
>
> Can you please post your complete code?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Thu, 1 Dec 2005 16:26:02 -0800, KL wrote:

>Here is the complete code i am using ....
>DECLARE
>@.SPName varchar(128) ,
>@.DateAndIntCols varchar(1000)
>SELECT @.SPName='eUpdated'
>SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
>SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
>And when i printed the variable @.DateAndIntCols its showing as
>'eUpdated,eDeleted'....
Hi KL,
Since I don;t have your table, I had to add some lines to the script.
Here's what I executed:
CREATE TABLE MetaData
(SPID char(4) NOT NULL PRIMARY KEY,
DateAndIntCols varchar(1000) NOT NULL)
INSERT INTO MetaData (SPID, DateAndIntCols)
VALUES ('CC-3', 'eUpdated,eDeleted')
go
DECLARE
@.SPName varchar(128) ,
@.DateAndIntCols varchar(1000)
SELECT @.SPName='eUpdated'
SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
go
DROP TABLE MetaData
go
And here's the output:
1
This is the expected output. What output are you getting? Also, what is
the output if you execute
SELECT @.@.VERSION
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

CHARINDEX in user-defined function

Hi,
I'm trying to use the CHARINDEX built-in function in my user-defined
function, but it doesn't seem to work.
Right now I got down to only write a test-function that doesn't do anything
else but call charindex:
CREATE FUNCTION X(@.p_str1 VARCHAR, @.p_str2 VARCHAR)
RETURNS INT AS
BEGIN
RETURN CHARINDEX(@.p_str1, @.p_str2);
END
Now, when I do a simple select like this:
SELECT NAME, dbo.X('Smith', NAME) AS Expr1, CHARINDEX('Smith', NAME) AS
Expr2 FROM PERSONS
The second column will be 0 everywhere, so rows like this occur:
John Smith, 0, 6
I've read that non-deterministic functions are not allowed to be used in
user-defined functions, and I suspect that this is what causes this.
In this case:
1. Why can't non-deterministic functions be used?
2. Shouldn't there be some error thrown in dbo.X() instead of it simply
returning 0?
3. Is there some similar deterministic function that can be used? I really
need this function for doing some basic parsing, I'm don't want write my own
in vain.
4. How on Earth can such a simple function be non-deterministic?
Thx
On Sat, 2 Oct 2004 21:39:44 +0200, Agoston Bejo wrote:

>CREATE FUNCTION X(@.p_str1 VARCHAR, @.p_str2 VARCHAR)
The default length for a varchar is 1 byte. You might want to change the
above line to something like:
CREATE FUNCTION X(@.p_str1 VARCHAR(20), @.p_str2 VARCHAR(50))
(20 and 50 used as example - se proper judgement to find the best length
for your function). This will solve your problem.
Now on to your questions:

>1. Why can't non-deterministic functions be used?
Because (unlike views) a user-defined function gets called multiple times
during the execution of one query; if the results change from call to
call, all kinds of nasty side-effects will kick in,

>2. Shouldn't there be some error thrown in dbo.X() instead of it simply
>returning 0?
If you attempt to create a user-defined function with a non-deterministic
function, you'll get an error message.

>3. Is there some similar deterministic function that can be used? I really
>need this function for doing some basic parsing, I'm don't want write my own
>in vain.
A workaround if you need to use a non-deterministic function in your
userdefined function is to put the nondeterministic function in a view and
reference the view from within the function. This can not be used for all
non-deterministic functions, but it's a common workaround for including
the current date and time (getdate()) in a UDF.

>4. How on Earth can such a simple function be non-deterministic?
It isn't. :-)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||> A workaround if you need to use a non-deterministic function in your
> userdefined function is to put the nondeterministic function in a view and
> reference the view from within the function. This can not be used for all
> non-deterministic functions, but it's a common workaround for including
> the current date and time (getdate()) in a UDF.
The view workaround is suspect also. See http://www.aspfaq.com/2439 for
other workarounds and a link to a discussion about the inconsistency of the
results from a view...
A
|||Hi!
What you told solved the problem completely. I have some minor questions
left, however:
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> az albbiakat rta a kvetkezo
hrzenetben: dc2ul09hsjru7d0der6jpqoe2cbics6e2m@.4ax.com...
> Because (unlike views) a user-defined function gets called multiple times
> during the execution of one query; if the results change from call to
> call, all kinds of nasty side-effects will kick in,
I see, only to be clear on the case: does 'non-deterministic' in this
terminology also mean that the function has side effects? It is possible
that a 'non-deterministic' function (in the classical sense) may not change
anything in the database.

> It isn't. :-)
From the MSDN:
"All built-in string functions, except for CHARINDEX and PATINDEX, are
deterministic."
(URL:
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
fa-fz_7oqb.asp - Transact-SQL Reference / Built-in functions)
But I didn't get any error message, and CHARINDEX worked indeed in my
function.
Is the MSDN lying?

>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Sat, 2 Oct 2004 17:45:06 -0400, Aaron [SQL Server MVP] wrote:

>The view workaround is suspect also. See http://www.aspfaq.com/2439 for
>other workarounds and a link to a discussion about the inconsistency of the
>results from a view...
Hi Aaron,
Thanks for the heads-up!
Good article - and a very interesting script in Tibor's proof. I ran it
several times and got 5, 6, 7, 8 or 9 results - and often, they were not
even distinct at all!!
I wish I had encountered this link before I tried to explain to the OP why
UDF's can't contain non-deterministic functions...
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Sun, 3 Oct 2004 11:12:17 +0200, Agoston Bejo wrote:

>Hi!
>What you told solved the problem completely. I have some minor questions
>left, however:
Hi Agoston,
I hope you also read the article posted by Aaron (in reply to my message)
which shows why the workaround with a view has some problems as well.
Please follow his advice instead of my original advice.
I'll type the answers to your minor questions inline.

>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> az albbiakat rta a kvetkezo
>hrzenetben: dc2ul09hsjru7d0der6jpqoe2cbics6e2m@.4ax.com...
>I see, only to be clear on the case: does 'non-deterministic' in this
>terminology also mean that the function has side effects? It is possible
>that a 'non-deterministic' function (in the classical sense) may not change
>anything in the database.
No, 'non-deterministic' in this terminology means *only* whether the
function is deterministic or not - in other words: if the result of the
function (with the same arguments) will always be the same or not.
Not having side effects is *another* requirement for user-defined
functions (UDFs). I've seen some workarounds to this requirement posted in
these newsgroups as well, but I'd recommend against using them - there is
some solid reasoning behind the requirement of not having side effects.
The main reason is the fact that the optimizer is free to choose different
query plans, that might involve more or less calls to the UDFs used in the
query, and (if columns are used as parameters) different sequences. The
result of the query becomes inpredictable.

>From the MSDN:
>"All built-in string functions, except for CHARINDEX and PATINDEX, are
>deterministic."
>(URL:
>http://msdn.microsoft.com/library/de...us/tsqlref/ts_
>fa-fz_7oqb.asp - Transact-SQL Reference / Built-in functions)
>But I didn't get any error message, and CHARINDEX worked indeed in my
>function.
>Is the MSDN lying?
It isn't lying. Unclear, yes. But not lying.
First, let me give you a quote that is more relevant for you. The URL is
http://msdn.microsoft.com/library/en...s_08_460j.asp.
(start quote)
Built-in functions that can return different data on each call are not
allowed in user-defined functions. The built-in functions not allowed in
user-defined functions are:
@.@.CONNECTIONS @.PACK_SENT GETDATE
@.@.CPU_BUSY @.PACKET_ERRORS GetUTCDate
@.@.IDLE @.TIMETICKS NEWID
@.@.IO_BUSY @.TOTAL_ERRORS RAND
@.@.MAX_CONNECTIONS @.@.TOTAL_READ TEXTPTR
@.@.PACK_RECEIVED @.@.TOTAL_WRITE
(end quote)
The quote you gave relates to determining whether your UDF will be
considered deterministic or non-deterministic. Since it contains a call to
CHARINDEX (which is considerde non-determinstic), your UDS will be
considered non-deterministic as well. This means that you can't create an
index on a view or computed column that is creating using this UDF. As
long as that is not your intention, it should not bother you.
(By the way - I do admit I often wondered WHY SQL Server regards CHARINDEX
and PATINDEX to be nondeterministic).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||For fun, give this a try (using the framework of Tibor's example):
SELECT foo, count(*) as ct
FROM (
SELECT dbo.foo1() as foo
FROM Northwind.dbo."Order Details" AS od
INNER JOIN Northwind.dbo.Orders AS o
ON o.OrderId = od.OrderID
) T
GROUP BY foo
You are likely to get a result set containing several rows, all of which
have the same value of foo. Even adding DISTINCT to the outer query
does not guarantee a result set with distinct rows.
SK
Hugo Kornelis wrote:

>On Sat, 2 Oct 2004 17:45:06 -0400, Aaron [SQL Server MVP] wrote:
>
>
>Hi Aaron,
>Thanks for the heads-up!
>Good article - and a very interesting script in Tibor's proof. I ran it
>several times and got 5, 6, 7, 8 or 9 results - and often, they were not
>even distinct at all!!
>I wish I had encountered this link before I tried to explain to the OP why
>UDF's can't contain non-deterministic functions...
>Best, Hugo
>
|||Hi,
I wrote the following replacement for CHARINDEX, which is
deterministic, albeit slower than the builtin CHARINDEX function. But
since it is deterministic, I can use it in indexed views, etc...
create function dbo.fn_CHARINDEX(@.ch char(1), @.s varchar(8000))
returns int with schemabinding as
begin
declare @.i int
set @.i = 1
while @.i <= len(@.s) begin
if substring(@.s,@.i,1) = @.ch
return @.i
set @.i = @.i + 1
end
return NULL
end
Cheers
Simon Kissane
http://simonkissane.blogspot.com/

charindex in user-defined function

Hi,
I'm trying to use the CHARINDEX built-in function in my user-defined
function, but it doesn't seem to work.
Right now I got down to only write a test-function that doesn't do anything
else but call charindex:
CREATE FUNCTION X(@.p_str1 VARCHAR, @.p_str2 VARCHAR)
RETURNS INT AS
BEGIN
RETURN CHARINDEX(@.p_str1, @.p_str2);
END
Now, when I do a simple select like this:
SELECT NAME, dbo.X('Smith', NAME) AS Expr1, CHARINDEX('Smith', NAME) AS
Expr2 FROM PERSONS
The second column will be 0 everywhere, so rows like this occur:
John Smith, 0, 6
I've read that non-deterministic functions are not allowed to be used in
user-defined functions, and I suspect that this is what causes this.
In this case:
1. Why can't non-deterministic functions be used?
2. Shouldn't there be some error thrown in dbo.X() instead of it simply
returning 0?
3. Is there some similar deterministic function that can be used? I really
need this function for doing some basic parsing, I'm don't want write my own
in vain.
4. How on Earth can such a simple function be non-deterministic?
Thx
The problem with your function is that you didn't define the size of the
input parameters, therefore your strings get truncated to VARCHAR(1). For
example, change it to:
CREATE FUNCTION X(@.p_str1 VARCHAR(30), @.p_str2 VARCHAR(30))
...
Some non-deterministic functions can be used in a UDF but others aren't
permitted, mainly I think because of the difficulties that could result from
using those functions in queries or in conjunction other features that might
reference the UDF.
Your system date is incorrect. You may not get many replies because people
who sort posts by date won't see your post as a recent one.
David Portas
SQL Server MVP

CHARINDEX in user-defined function

Hi,
I'm trying to use the CHARINDEX built-in function in my user-defined
function, but it doesn't seem to work.
Right now I got down to only write a test-function that doesn't do anything
else but call charindex:
CREATE FUNCTION X(@.p_str1 VARCHAR, @.p_str2 VARCHAR)
RETURNS INT AS
BEGIN
RETURN CHARINDEX(@.p_str1, @.p_str2);
END
Now, when I do a simple select like this:
SELECT NAME, dbo.X('Smith', NAME) AS Expr1, CHARINDEX('Smith', NAME) AS
Expr2 FROM PERSONS
The second column will be 0 everywhere, so rows like this occur:
John Smith, 0, 6
I've read that non-deterministic functions are not allowed to be used in
user-defined functions, and I suspect that this is what causes this.
In this case:
1. Why can't non-deterministic functions be used?
2. Shouldn't there be some error thrown in dbo.X() instead of it simply
returning 0?
3. Is there some similar deterministic function that can be used? I really
need this function for doing some basic parsing, I'm don't want write my own
in vain.
4. How on Earth can such a simple function be non-deterministic?
Thx
On Sat, 2 Oct 2004 21:39:44 +0200, Agoston Bejo wrote:

>CREATE FUNCTION X(@.p_str1 VARCHAR, @.p_str2 VARCHAR)
The default length for a varchar is 1 byte. You might want to change the
above line to something like:
CREATE FUNCTION X(@.p_str1 VARCHAR(20), @.p_str2 VARCHAR(50))
(20 and 50 used as example - se proper judgement to find the best length
for your function). This will solve your problem.
Now on to your questions:

>1. Why can't non-deterministic functions be used?
Because (unlike views) a user-defined function gets called multiple times
during the execution of one query; if the results change from call to
call, all kinds of nasty side-effects will kick in,

>2. Shouldn't there be some error thrown in dbo.X() instead of it simply
>returning 0?
If you attempt to create a user-defined function with a non-deterministic
function, you'll get an error message.

>3. Is there some similar deterministic function that can be used? I really
>need this function for doing some basic parsing, I'm don't want write my own
>in vain.
A workaround if you need to use a non-deterministic function in your
userdefined function is to put the nondeterministic function in a view and
reference the view from within the function. This can not be used for all
non-deterministic functions, but it's a common workaround for including
the current date and time (getdate()) in a UDF.

>4. How on Earth can such a simple function be non-deterministic?
It isn't. :-)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||> A workaround if you need to use a non-deterministic function in your
> userdefined function is to put the nondeterministic function in a view and
> reference the view from within the function. This can not be used for all
> non-deterministic functions, but it's a common workaround for including
> the current date and time (getdate()) in a UDF.
The view workaround is suspect also. See http://www.aspfaq.com/2439 for
other workarounds and a link to a discussion about the inconsistency of the
results from a view...
A
|||Hi!
What you told solved the problem completely. I have some minor questions
left, however:
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> az albbiakat rta a kvetkezo
hrzenetben: dc2ul09hsjru7d0der6jpqoe2cbics6e2m@.4ax.com...
> Because (unlike views) a user-defined function gets called multiple times
> during the execution of one query; if the results change from call to
> call, all kinds of nasty side-effects will kick in,
I see, only to be clear on the case: does 'non-deterministic' in this
terminology also mean that the function has side effects? It is possible
that a 'non-deterministic' function (in the classical sense) may not change
anything in the database.

> It isn't. :-)
From the MSDN:
"All built-in string functions, except for CHARINDEX and PATINDEX, are
deterministic."
(URL:
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
fa-fz_7oqb.asp - Transact-SQL Reference / Built-in functions)
But I didn't get any error message, and CHARINDEX worked indeed in my
function.
Is the MSDN lying?

>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Sat, 2 Oct 2004 17:45:06 -0400, Aaron [SQL Server MVP] wrote:

>The view workaround is suspect also. See http://www.aspfaq.com/2439 for
>other workarounds and a link to a discussion about the inconsistency of the
>results from a view...
Hi Aaron,
Thanks for the heads-up!
Good article - and a very interesting script in Tibor's proof. I ran it
several times and got 5, 6, 7, 8 or 9 results - and often, they were not
even distinct at all!!
I wish I had encountered this link before I tried to explain to the OP why
UDF's can't contain non-deterministic functions...
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Sun, 3 Oct 2004 11:12:17 +0200, Agoston Bejo wrote:

>Hi!
>What you told solved the problem completely. I have some minor questions
>left, however:
Hi Agoston,
I hope you also read the article posted by Aaron (in reply to my message)
which shows why the workaround with a view has some problems as well.
Please follow his advice instead of my original advice.
I'll type the answers to your minor questions inline.

>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> az albbiakat rta a kvetkezo
>hrzenetben: dc2ul09hsjru7d0der6jpqoe2cbics6e2m@.4ax.com...
>I see, only to be clear on the case: does 'non-deterministic' in this
>terminology also mean that the function has side effects? It is possible
>that a 'non-deterministic' function (in the classical sense) may not change
>anything in the database.
No, 'non-deterministic' in this terminology means *only* whether the
function is deterministic or not - in other words: if the result of the
function (with the same arguments) will always be the same or not.
Not having side effects is *another* requirement for user-defined
functions (UDFs). I've seen some workarounds to this requirement posted in
these newsgroups as well, but I'd recommend against using them - there is
some solid reasoning behind the requirement of not having side effects.
The main reason is the fact that the optimizer is free to choose different
query plans, that might involve more or less calls to the UDFs used in the
query, and (if columns are used as parameters) different sequences. The
result of the query becomes inpredictable.

>From the MSDN:
>"All built-in string functions, except for CHARINDEX and PATINDEX, are
>deterministic."
>(URL:
>http://msdn.microsoft.com/library/de...us/tsqlref/ts_
>fa-fz_7oqb.asp - Transact-SQL Reference / Built-in functions)
>But I didn't get any error message, and CHARINDEX worked indeed in my
>function.
>Is the MSDN lying?
It isn't lying. Unclear, yes. But not lying.
First, let me give you a quote that is more relevant for you. The URL is
http://msdn.microsoft.com/library/en...s_08_460j.asp.
(start quote)
Built-in functions that can return different data on each call are not
allowed in user-defined functions. The built-in functions not allowed in
user-defined functions are:
@.@.CONNECTIONS @.PACK_SENT GETDATE
@.@.CPU_BUSY @.PACKET_ERRORS GetUTCDate
@.@.IDLE @.TIMETICKS NEWID
@.@.IO_BUSY @.TOTAL_ERRORS RAND
@.@.MAX_CONNECTIONS @.@.TOTAL_READ TEXTPTR
@.@.PACK_RECEIVED @.@.TOTAL_WRITE
(end quote)
The quote you gave relates to determining whether your UDF will be
considered deterministic or non-deterministic. Since it contains a call to
CHARINDEX (which is considerde non-determinstic), your UDS will be
considered non-deterministic as well. This means that you can't create an
index on a view or computed column that is creating using this UDF. As
long as that is not your intention, it should not bother you.
(By the way - I do admit I often wondered WHY SQL Server regards CHARINDEX
and PATINDEX to be nondeterministic).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||For fun, give this a try (using the framework of Tibor's example):
SELECT foo, count(*) as ct
FROM (
SELECT dbo.foo1() as foo
FROM Northwind.dbo."Order Details" AS od
INNER JOIN Northwind.dbo.Orders AS o
ON o.OrderId = od.OrderID
) T
GROUP BY foo
You are likely to get a result set containing several rows, all of which
have the same value of foo. Even adding DISTINCT to the outer query
does not guarantee a result set with distinct rows.
SK
Hugo Kornelis wrote:

>On Sat, 2 Oct 2004 17:45:06 -0400, Aaron [SQL Server MVP] wrote:
>
>
>Hi Aaron,
>Thanks for the heads-up!
>Good article - and a very interesting script in Tibor's proof. I ran it
>several times and got 5, 6, 7, 8 or 9 results - and often, they were not
>even distinct at all!!
>I wish I had encountered this link before I tried to explain to the OP why
>UDF's can't contain non-deterministic functions...
>Best, Hugo
>
|||Hi,
I wrote the following replacement for CHARINDEX, which is
deterministic, albeit slower than the builtin CHARINDEX function. But
since it is deterministic, I can use it in indexed views, etc...
create function dbo.fn_CHARINDEX(@.ch char(1), @.s varchar(8000))
returns int with schemabinding as
begin
declare @.i int
set @.i = 1
while @.i <= len(@.s) begin
if substring(@.s,@.i,1) = @.ch
return @.i
set @.i = @.i + 1
end
return NULL
end
Cheers
Simon Kissane
http://simonkissane.blogspot.com/