UserDefinedFunction
-
Sorry if this is in the wrong place...
I have a UDF that I am trying to calling from within a SELECT statement, which works fine. If I try and call it a second time from with in the same SELECT, but pass it different information, it returns the first value both times.
EG. SELECT ISNULL(dbo.fnc_GetManagerForOrgIdAndDate(VSS.IFA, 56, VSS.OriginalSourceCreatedDate), '<Non-Panel>'),
VSS.IFA [IFANo],
CASE WHEN LBP.lbp_tDescription IS NULL THEN 'C' ELSE LBP.lbp_tDescription END [BUSPOTENTIAL],
ISNULL(dbo.fnc_GetManagerForOrgIdAndDate(VSS.IFA, 45, VSS.OriginalSourceCreatedDate), '<Non-Panel>')
.......
Basically it should return 2 different records, but it returns the same answer, both times it is called.
Is this correct? If so, is the easiest fix to create a new UDF?
Thanks,
Graham
-
There must be a bug in the function. We need to see the source code.
-
CREATE FUNCTION dbo.fnc_GetManagerForOrgIdAndDate (@OrgId int, @BusinessRoleId int, @Date datetime)
RETURNS varchar(64)
AS
BEGIN
DECLARE @IndividualId int
DECLARE @ManagerName varchar(64)
DECLARE @BusinessRoles Table (BusRoleId int)
IF @BusinessRoleId = 45
BEGIN
INSERT @BusinessRoles SELECT 57
INSERT @BusinessRoles SELECT 58
INSERT @BusinessRoles SELECT 59
END
ELSE
INSERT @BusinessRoles SELECT @BusinessRoleId
SELECT
@IndividualId = OGI.ogi_lIndividualId,
@ManagerName = IND.ind_tFirstName + ' ' + IND.ind_tLastName
FROM
dbo.jct_OrgIndividual OGI
INNER JOIN
(SELECT
Max(ogi_lid) as MaxID
FROM
dbo.jct_OrgIndividual
WHERE
ogi_dStartDate <= @Date and ogi_dEndDate >= @Date
and ogi_lOrganisationId = @OrgId
and ogi_lBusinessRoleId in (Select BusRoleId From @BusinessRoles)) Latest
ON OGI.ogi_lid = Latest.MaxID
INNER JOIN dbo.tbl_individual IND
ON OGI.ogi_lIndividualId = IND.ind_lid
AND IND.ind_bisinternal = 1
WHERE
ogi_dStartDate <= @Date and ogi_dEndDate >= @Date
and ogi_lOrganisationId = @OrgId
and ogi_lBusinessRoleId in (Select BusRoleId From @BusinessRoles)
RETURN @ManagerName
END
-
So the difference between the two function calls is @BusinessRoleId, which again means that the @BusinessRoles table is different. Could the problem be that the select in your function sometimes returns more than one row? If so, @ManagerName will (normally) be the last row in the (implicit) ordering of the records, and this row might always be the same. You can easily test this by replacing
RETURN @ManagerName
by
RETURN cast(@@ROWCOUNT as varchar)
and then run your query. If the function now returns something larger than 1, you should probably add an "order by" to your select.
Hope this helps....
-
sounds good, will test.
Thanks for the responses.
-
0 and 1 s only being returned.
I am going to create as 2nd function for now, as a temporary workaround, until this can be sorted out.
-
So that wasn't the problem
Ifselect dbo.fnc_GetManagerForOrgIdAndDate(VSS.IFA, 56, VSS.OriginalSourceCreatedDate), dbo.fnc_GetManagerForOrgIdAndDate(VSS.IFA, 45, VSS.OriginalSourceCreatedDate)
from....
returns the same, and it shouldn't, then it must be the logic in your function that is wrong...
-
I believe the function is correct. I have created a second function, which is exactly the same, except for the name and now the correct values are being returned correctly.
-
Just out of curiosity, if you edit the original function and enclose the "INSERT @BusinessRoles SELECT @BusinessRoleId" in a BEGIN - END block, will the problem persist or not? I've learned to wrap all IF/ELSE statements in BEGIN-END blocks, even if it is a single command. Also, if there are any comments in the actual code beginning with --, replace them with /* */. Sometimes it happens that the end-of-line character, which signals end of comments starting with --, goes missing for some reason, and the entire next row is interpreted as comments, i.e. skipped.
Otherwise, I don't see how it can be possible that you write two identical functions, giving different results.
-
good idea, and it is usually some that I insist on as well.
But no luck....
Thanks for all the advise.
-
Out of curiosity.
Did you check syscomments text for both functions to verify they are absolutelly the same?
The second function, did you just copied and pasted the code into the new one or retyped the whole thing?
So if the second function is identical as the first one - is the problem it in the name itself? Does it behave the same way even if you recompile it?
I had once a problem with SPs ( this was on 6.5 so it was a while ago... and probably not an issue here) where the SP got corrupted because there were too many lines of comments on the top - remember the script generated line with the SP name? The developer rerun the script 8 or 9 times and never removed the line and finally the SP went belly up.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply