Function returns NULL

[hexOffender]

I wrote this function to calculate the turnaround time for a dictation. When I run just the query, the turnaround time gets calculated just fine. But when I try to call the function, with the same parameters, it returns NULL.

Here is the Function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Turnaround](@VisitID varchar(15),@QueryID1 varchar(10),@QueryID2 varchar(10),@ReportName varchar(20))
RETURNS int
AS
BEGIN
--Declare @Turnaround minute
return(SELECT distinct DATEDIFF(minute,(SELECT distinct Response
FROM NurQueryResults AS NQR
INNER JOIN AbstractData AS AD
ON NQR.SourceID = AD.SourceID AND
NQR.VisitID = AD.VisitID AND
--AD.UnitNumber = @UnitNum
NQR.VisitID=@VisitID
WHERE NQR.QueryID = @QueryID1)+ ' '+ --Parameter QueryID1 -NUR.BDAY or NUR.ORPDTE
(SELECT STUFF((SELECT distinct Response
FROM NurQueryResults AS NQR
INNER JOIN AbstractData AS AD
ON NQR.SourceID = AD.SourceID AND
NQR.VisitID = AD.VisitID AND
--AD.UnitNumber = @UnitNum
NQR.VisitID=@VisitID
WHERE NQR.QueryID = @QueryID2),3,0,':')+':00.000'),OeResults1.DictDateTime)--Parameter QueryID2
--NUR.TIME or NUR.ORTRR
AS Turnaround --BirthDateTime , OeResults1.DictDateTime
From NurQueryResults AS NQR
Inner Join AbstractData as AD on NQR.SourceID = AD.SourceID and NQR.VisitID = AD.VisitID
Inner Join OeResults ON AD.VisitID=OeResults.PatientVisitID
Inner Join OeResults1 ON OeResults1.ResultID=OeResults.ResultID
WHERE (NQR.VisitID=@VisitID)and OeResults.ReportName=@ReportName)


END

Here is the main part of the function:

SELECT distinct DATEDIFF(minute,(SELECT distinct Response
FROM NurQueryResults AS NQR
INNER JOIN AbstractData AS AD
ON NQR.SourceID = AD.SourceID AND
NQR.VisitID = AD.VisitID AND
--AD.UnitNumber = @UnitNum
NQR.VisitID='V1002108511'
WHERE NQR.QueryID = 'NUR.BDAY')+ ' '+ --Parameter QueryID1 -NUR.BDAY or NUR.ORPDTE
(SELECT STUFF((SELECT distinct Response
FROM NurQueryResults AS NQR
INNER JOIN AbstractData AS AD
ON NQR.SourceID = AD.SourceID AND
NQR.VisitID = AD.VisitID AND
--AD.UnitNumber = @UnitNum
NQR.VisitID='V1002108511'
WHERE NQR.QueryID ='NUR.TIME'),3,0,':')+':00.000'),OeResults1.DictDateTime)--Parameter QueryID2
--NUR.TIME or NUR.ORTRR
AS Turnaround --BirthDateTime , OeResults1.DictDateTime
From NurQueryResults AS NQR
Inner Join AbstractData as AD on NQR.SourceID = AD.SourceID and NQR.VisitID = AD.VisitID
Inner Join OeResults ON AD.VisitID=OeResults.PatientVisitID
Inner Join OeResults1 ON OeResults1.ResultID=OeResults.ResultID
WHERE (NQR.VisitID='V1002108511')and OeResults.ReportName='*NEWBORN EXAMINATION*'

If I call the function say
" Select dbo.Turnaround('V1002108511','NUR.BDAY','NUR.TIME','*NEWBORN EXAMINATION*')" it will return NULL. The query for the Function works, but when I try to use the function, it only returns NULLs.

 

[markros]

You wrote a scalar function that should return int. If you wanted to return a query, you should have written table valued function. And if you wanted to return an integer, you should return an integer value from your function.

PluralSight Learning Library

 

[hexOffender]

It does return int, so is scalar the wrong type of function ?
It worked fine for another set of parameters, but wont work for these.

 

[markros]

Post your function code again. In the code you posted I don't see RETURN @SomeIntValue statement.

If the function is supposed to return one value, then scalar type is correct. If the function should return select statement, then it should be table valued function.

PluralSight Learning Library

 

[gmmastros]

Here's your problem...

[tt]
ALTER FUNCTION [dbo].[Turnaround](@VisitID varchar(15),@QueryID1 varchar(10),@QueryID2 varchar(10),@ReportName varchar([!]20[/!]))[/tt]

If I call the function say
" Select dbo.Turnaround('V1002108511','NUR.BDAY','NUR.TIME','[!]*NEWBORN EXAMINATION*[/!]')"

Your report name is a 21 character long string.

[tt]Select Len('*NEWBORN EXAMINATION*')[/tt]

Since your parameter is a varchar(20), @ReportName is getting truncated to 20 characters, causing your query to not match any rows, resulting in a NULL output.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Interesting. Are you saying that
return select intValue is perfectly OK? I was thinking we need to get our value into the variable first and then return it using this variable.

PluralSight Learning Library

 

[hexOffender]

YES, Thank You George, that was exactly it. It made sense to me as soon as I read your post because the other parameter's report name was shorter. I just ran it and it works perfectly. Sometimes it just takes a second set of Eyes.

 

[gmmastros]

Markros, you do not need to assign a variable. In fact, it actually slows down the function (albeit slightly).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I see. Learn something new every day.

PluralSight Learning Library