Function only returns null - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Function only returns null

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-07 : 05:05:58

Hi

Here is what I am trying to do:

I have a column with data like this:


Column
======
Data1
Data2
null
data5, data4
null
data3
null

I have a function where I want to split the comma delimited rows and am trying this with the following function but only get nulls. For null columns I just want to leave as is. Here is the code:


select dbo.splitstring(column)
from table

create function splitstring(@ID VARCHAR(510))
returns varchar(510)
AS
BEGIN
declare @Return VARCHAR(510)
  IF CHARINDEX(',', @ID, 0) <> 0 AND CHARINDEX('<Condition>', @ID, 0) <> 0 
  SET @Return = substring(@ID, CHARINDEX(' ', @ID, 0) + 1, len(@ID))
  else 
  SET @Return = @ID
  return @Return
end

Output
======
null
null
null
null
null
null
...
...

Not sure if this is right coding or logic to be honest and the query takes about 3 minutes to complete, there are 14000 rows to process.

Thanks

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 05:16:03

try using this spilt function

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

and use it like


select f.Val
FROM table t 
cross apply dbo.ParseValues(t.Column,',')f

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-07 : 05:33:56

quote:
Originally posted by visakh16

try using this spilt function

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

and use it like

select f.Val
FROM table t
cross apply dbo.ParseValues(t.Column,',')f

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

For the query I can only get:

select dbo.MyFunction(column1)
from table1

when you say f.val are you referring to the internal table in the function. If so I try to put:

select myfunction.val

and it doesn't work nor does cross apply

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 06:30:25

f.val is the value returned by Visakh's function.

If you want to see all the columns from your table too then do:


select f.Val, t.*
FROM table t 
cross apply dbo.ParseValues(t.Column,',')f

You need to change "table" and "column" to the names of your table, and the column your want to split, respectively

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-07 : 07:52:06

Got some errors, here are the descriptions given and I have underlined the parts where the squiggly underline appears:

select f.val <<< the multipart identifier could not be bound
from dbo.table s
cross apply [dbo].[function](s.column, ',')f <<< Invalid object name dbo.function

Here's my function code:

if exists (select * from sysobjects where name = N'function')

   drop function function

go

CREATE FUNCTION [dbo].[Siebel]
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN 
LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) 
ELSE @String 
END, 
@String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN 
SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) 
ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 08:52:10

[code]
cross apply [dbo].[function](s.column, ',')f <<< Invalid object name dbo.function
[/code]
You've tried to use a function called "function", but:
[code]
CREATE FUNCTION [dbo].[Siebel]
[/code]
you created it with the name "Siebel"

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-07 : 08:54:08

Hi

That's a typo that is real name siebel but have replicated the name with 'function' so it is not that.


CREATE FUNCTION [dbo].[function]
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN 
LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) 
ELSE @String 
END, 
@String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN 
SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) 
ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 08:54:23

And this is dropping a function called "function", so your CREATE will give "already exists" error if you try to run it again:


if exists (select * from sysobjects where name = N'function')
   drop function function
go

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 08:59:23

Can't imagine why you would want to call it "function", but it works fine here:


DECLARE @MyTable TABLE
(
	MyColumn	varchar(100)
)

INSERT INTO @MyTable
SELECT 'Data1' UNION ALL
SELECT 'Data2' UNION ALL
SELECT null UNION ALL
SELECT 'data5, data4' UNION ALL
SELECT null UNION ALL
SELECT 'data3' UNION ALL
SELECT null

select f.val
from @MyTable s
cross apply [dbo].[function](s.MyColumn, ',')f 

OUTPUT:
val                                                
-------------------------------------------------- 
Data1
Data2
data5
 data4
data3

(5 row(s) affected)

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-07 : 09:32:51

I have not called it function I have used this name to highlight that this is a function I am using.

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 10:10:28

I ran your code "CREATE FUNCTION [dbo].[function]", as you posted it, and the code I posted above and it works.

If you keep changing the names of everything we haven't any chance of working out what you are doing wrong.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-07 : 10:19:42

quote:
Originally posted by Kristen

And this is dropping a function called "function", so your CREATE will give "already exists" error if you try to run it again:

if exists (select * from sysobjects where name = N'function')
drop function function
go

How will this give this error? I have not had this error once yet?

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 10:32:39

Because the code that you SAID that you had used, and which you posted above, is:


if exists (select * from sysobjects where name = N'function')
   drop function function
go

CREATE FUNCTION [dbo].[Siebel]

but you've since said you've messed around with the names, and thus the time I have spent trying to help you is pretty much wasted.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-07 : 12:04:36

No I left out the alias name I was using for illustration purposes, this does not detract from the problem I am having and this has not led to any further insight into what is the problem.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-07 : 16:44:42

So what problem are you actually having then? This thread is very confusing with you changing names on us. Let's start over. Post your code, don't change names, and describe what the problem is.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-08 : 04:31:14

It's geting confusing because people are making assumptions and creating problems that are not there in the first place. I described the problem clearly in that I am not returning any data from a call to a function.

Kristen
Test

22859 Posts

Posted - 2011-09-08 : 06:10:18

Kindly just do this:

"Let's start over. Post your code, don't change names, and describe what the problem is."

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-09-08 : 06:40:29

Lets confuse this a little more.

If you are using SQL 2005 or above, use a CLR to do the splitting for you.

http://www.sommarskog.se/arrays-in-sql-2005.html#CLR


declare @tab Table (id int, val varchar(100))

insert into @tab
select 1, 'Data1'
union select 2, 'Data2'
union select 3, null
union select 4, 'data5, data4'
union select 5, null
union select 6, 'data3'

SELECT
	a.id, b.str  
FROM 
	@tab a
OUTER APPLY
	CLR_charlist_iter (a.val,',') b

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-08 : 17:21:59

quote:
Originally posted by Grifter

It's geting confusing because people are making assumptions and creating problems that are not there in the first place. I described the problem clearly in that I am not returning any data from a call to a function.

We understand what your original problem is, but we are directing you to use a different function that will work. So we need to get that working. We are so focused on the object name because of your comment about the errors. We have to address those errors first. One of your errors is about an invalid object, and it appears to be because you are using a different name. So let's start over with everything. Show us exactly what function you are now using (don't change any names), show us how you are calling it, and then clearly show us all of the errors/issues.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources