June 24, 2003 at 12:34 am
Situation: I have SQL user defined function which accepts parameter & returns string ceated and executed on SQL server 2000
Complication: Today I execute the function and run the function passing parameters works fine. If I run the same function passing parameters next day, it returns null.
If I again execute the User defined function created by me and then run the function with parameters it works.
Question: Can anybody tell me why I need to keep executing the User defined function to get the results?
Answers: I expect from you all.
June 24, 2003 at 1:18 am
Sorry no answer yet, but cold you post your function and the way you want to runit. I assume with
quote:
I again execute the User defined function created by me
you mean that you recreate the function or do I understand you wrong ???
[font="Verdana"]Markus Bohse[/font]
June 24, 2003 at 1:18 am
Sorry no answer yet, but cold you post your function and the way you want to run it. I assume with
quote:
I again execute the User defined function created by me
you mean that you recreate the function or do I understand you wrong ???
[font="Verdana"]Markus Bohse[/font]
June 24, 2003 at 2:35 am
Hi, here is the sample code...
---------------------------------
if exists (select * from sysobjects where id = object_id('dbo.getStaffNamesForPersonalReferences') and sysstat & 0xf = 0)
drop function dbo.getStaffNamesForPersonalReferences
GO
CREATE FUNCTION dbo.getStaffNamesForPersonalReferences(@PERSONAL_REFERENCES varchar(2000))
RETURNS varchar(3000)
AS
BEGIN
declare
@iNPos int,
@strTemp varchar(2000),
@sPersonal_Ref varchar(9),
@sStaffName varchar(100),
@sStaffNames varchar(3000)
Select @iNPos = 1
select @strTemp=@PERSONAL_REFERENCES
While LEN(@strTemp) > 0 and @iNPos > 0
Begin
-- Fetch the position of the comma
SELECT @iNPos=PATINDEX('%,%',@strTemp)
if (@iNPos > 0)
begin
select @sPersonal_Ref=ltrim(rtrim(substring(@strTemp,1,@iNPos-1)))
select @strTemp=ltrim(rtrim(substring(@strTemp,@iNPos+1, LEN(@strTemp) )))
end
else
begin
select @sPersonal_Ref=ltrim(rtrim(substring(@strTemp,1,LEN(@strTemp) )))
select @iNPos=0
end
-- Fetch the name for this personal reference
if ltrim(rtrim(@sPersonal_Ref)) <> ''
begin
set @sStaffName = ''
select @sStaffName = FIRST_NAME + ' ' + SURNAME
from GLB.dbo.StaffMembers
where PERSONAL_REFERENCE = ltrim(rtrim(@sPersonal_Ref))
and CURRENT_RECORD = 1
if ltrim(rtrim(@sStaffName)) <> ''
begin
if (@sStaffNames <> '')
set @sStaffNames = @sStaffNames + ', ' + @sStaffName
else
set @sStaffNames = @sStaffName
end
end
End
return @sStaffNames
END
GO
---------------------------------
quote:
Situation: I have SQL user defined function which accepts parameter & returns string ceated and executed on SQL server 2000Complication: Today I execute the function and run the function passing parameters works fine. If I run the same function passing parameters next day, it returns null.
If I again execute the User defined function created by me and then run the function with parameters it works.
Question: Can anybody tell me why I need to keep executing the User defined function to get the results?
Answers: I expect from you all.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply