UDF 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

SQL Server 2005 Forums

Transact-SQL (2005)

UDF Returns Null

Author

Topic

stormcandi
Starting Member

46 Posts

Posted - 2009-05-20 : 17:42:29

Can someone please assist? I am using this UDF but it keeps returning null when I call it inside a Case statement of a Stored Procedure. I cannot figure out why since when I call the UDF directly and pass a static string in I get results back.

This is the query in the Stored Procedure calling the UDF:

    Select
		RecID,
		Case When (CharIndex(GrNinth, '/') = 0)
			Then dbo.CoursePlanFlowName(GrNinth)
			Else dbo.MultCoursePlanFlowNames(GrNinth)
			End as GrNinth,
		Case When (CharIndex(GrTenth, '/') = 0)
			Then dbo.CoursePlanFlowName(GrTenth)
			Else dbo.MultCoursePlanFlowNames(GrTenth)
			End as GrTenth,
		Case When (CharIndex(GrEleventh, '/') = 0)
			Then dbo.CoursePlanFlowName(GrEleventh)
			Else dbo.MultCoursePlanFlowNames(GrEleventh)
			End as GrEleventh,
		Case When (CharIndex(GrTwelfth, '/') = 0)
			Then dbo.CoursePlanFlowName(GrTwelfth)
			Else dbo.MultCoursePlanFlowNames(GrTwelfth)
			End as GrTwelfth,
		SubArea,
		Active, 
		IBOnly
	From CoursePlanFlow
	Where SubArea = @SubArea

This is the UDF being called:

ALTER FUNCTION [dbo].[MultCoursePlanFlowNames]
(
	@CID nvarchar(1000)
)
RETURNS nvarchar(4000)
AS
BEGIN
	
	Declare @ReturnVariable as nvarchar(4000)
	Declare @CID1 as varchar(9)
	Declare @CID2 as varchar(9)
	Declare @CName1 as nvarchar(1000)
	Declare @CName2 as nvarchar(1000)

	Select @CID1 = Left(@CID, CharIndex('/', @CID)-1)
	Select @CID2 = Right(@CID, CharIndex('/', @CID)- 1)

	Select 
		@CName1 = ((Convert(varchar(10), Course.CID)) + ' ' + Course.CName + ' ' + Convert(varchar(4), Course.CourseCredit) + ' credit(s)') 
	From PUSD.dbo.Course
	Where CID = @CID1
	Select 
		@CName2 = ((Convert(varchar(10), Course.CID)) + ' ' + Course.CName + ' ' + Convert(varchar(4), Course.CourseCredit) + ' credit(s)') 
	From PUSD.dbo.Course
	Where CID = @CID2



	Select @ReturnVariable = @CName1 + '/' + @CName2
	
	Return @ReturnVariable


END

Thanks in advance for any assistance you can provide

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-20 : 17:48:17

Try this version:

ALTER FUNCTION [dbo].[MultCoursePlanFlowNames]
(
	@CID nvarchar(1000)
)
RETURNS nvarchar(4000)
AS
BEGIN
	
	Declare @ReturnVariable as nvarchar(4000)
	Declare @CID1 as varchar(9)
	Declare @CID2 as varchar(9)
	Declare @CName1 as nvarchar(1000)
	Declare @CName2 as nvarchar(1000)

	Select @CID1 = Left(@CID, CharIndex('/', @CID)-1)
	Select @CID2 = Substring(@CID, CharIndex('/', @CID)+1,9)

	Select 
		@CName1 = ((Convert(varchar(10), Course.CID)) + ' ' + Course.CName + ' ' + Convert(varchar(4), Course.CourseCredit) + ' credit(s)') 
	From PUSD.dbo.Course
	Where CID = @CID1
	Select 
		@CName2 = ((Convert(varchar(10), Course.CID)) + ' ' + Course.CName + ' ' + Convert(varchar(4), Course.CourseCredit) + ' credit(s)') 
	From PUSD.dbo.Course
	Where CID = @CID2



	Select @ReturnVariable = @CName1 + '/' + @CName2
	
	Return @ReturnVariable


END

stormcandi
Starting Member

46 Posts

Posted - 2009-05-20 : 19:26:52

It still returns a null when in the Stored Procedure.

quote:

Originally posted by robvolk

Try this version:

ALTER FUNCTION [dbo].[MultCoursePlanFlowNames]
(
	@CID nvarchar(1000)
)
RETURNS nvarchar(4000)
AS
BEGIN
	
	Declare @ReturnVariable as nvarchar(4000)
	Declare @CID1 as varchar(9)
	Declare @CID2 as varchar(9)
	Declare @CName1 as nvarchar(1000)
	Declare @CName2 as nvarchar(1000)

	Select @CID1 = Left(@CID, CharIndex('/', @CID)-1)
	Select @CID2 = Substring(@CID, CharIndex('/', @CID)+1,9)

	Select 
		@CName1 = ((Convert(varchar(10), Course.CID)) + ' ' + Course.CName + ' ' + Convert(varchar(4), Course.CourseCredit) + ' credit(s)') 
	From PUSD.dbo.Course
	Where CID = @CID1
	Select 
		@CName2 = ((Convert(varchar(10), Course.CID)) + ' ' + Course.CName + ' ' + Convert(varchar(4), Course.CourseCredit) + ' credit(s)') 
	From PUSD.dbo.Course
	Where CID = @CID2



	Select @ReturnVariable = @CName1 + '/' + @CName2
	
	Return @ReturnVariable


END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-20 : 19:42:34

Run DBCC FREEPROCCACHE and execute your procedure with WITH RECOMPILE, see if that fixes it.

stormcandi
Starting Member

46 Posts

Posted - 2009-05-21 : 11:18:24

I understand that the DBCC FREEPROCCACHE frees up the cache. But before I run this, does it affect anyone else using the server and do I execute it in the Stored Procedure that calls the UDF everytime?

quote:
Originally posted by robvolk

Run DBCC FREEPROCCACHE and execute your procedure with WITH RECOMPILE, see if that fixes it.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-21 : 11:26:38

is it possible that you are passing it a NULL from your select statement?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

stormcandi
Starting Member

46 Posts

Posted - 2009-05-21 : 11:31:44

I have checked to make sure that I am not sending a null. The fields that are being sent to be handled by the UDF do have values in it.

quote:
Originally posted by Transact Charlie

is it possible that you are passing it a NULL from your select statement?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sheki
Starting Member

2 Posts

Posted - 2009-05-21 : 12:02:16

But could either @CName1 or @CName2 be null? if either one is null, you will get a null back. Try wrapping them like ISNULL(@CName1,'') + '/' + ISNULL(@CName2, '')

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-05-21 : 12:15:16

check to see if it's possible that Course.CourseCredit can be null. If so, you probably want to wrap an ISNULL around it.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

stormcandi
Starting Member

46 Posts

Posted - 2009-05-21 : 12:42:53

Course.CourseCredit is not returning a null and I added the ISNULL(@CName, '') to the ReturnVariable Area but still no go. I just cannot figure out why if I take it out of the Case Statement it works. But the reason it is in the Case Statement is to handle fields that do not have a slash in it.

quote:
Originally posted by jholovacs

check to see if it's possible that Course.CourseCredit can be null. If so, you probably want to wrap an ISNULL around it.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-21 : 13:23:26

Crap, should've seen this before, the Charindex is backwards, it should be:

Case When (CharIndex('/',GrNinth) = 0)

Change all of those expressions and see if that fixes it.

stormcandi
Starting Member

46 Posts

Posted - 2009-05-21 : 13:27:41

THANK YOU! That fixed it!

quote:
Originally posted by robvolk

Crap, should've seen this before, the Charindex is backwards, it should be:
Case When (CharIndex('/',GrNinth) = 0)
Change all of those expressions and see if that fixes it.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-22 : 04:29:37

Good catch Rob.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources