Hello,
I am unable to resolve an error message for following user defined function.
Scope of this function:
This functions generates a unique identifier no from a table and returns it to caller.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION SystemNoShiva1
( -- Add the parameters for the function here
)
RETURNS int
AS
begin
DECLARE @.st2 numeric(18,0)
DECLARE @.sys01 numeric(18,0)
declare sys cursor for Select Max(SystemNo) From TISSystemMst1
open sys
Fetch sys into @.sys01
begin
--DECLARE @.st2 numeric(18,0)
if (@.@.fetch_status = 0)
-- IF @.@.ROWCOUNT <> 0
close sys
Deallocate sys
select @.sys01 = @.sys01 + 1
--update TISSystemMst1 set systemno = (@.sys01)
insert into TISSystemMst1 values(@.sys01)
select @.st2 = @.sys01
end
RETURN @.st2
end
Messeg I receive is
Msg 128, Level 15, State 1, Line 17
The name "sys" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
How can I clear above error message?
Nilkanth Desai
Hi,
A typical cursor fetch as follows, however looking at the script, why are you using a cursor, if we are simply selecting the topmost (Max) value and incrementing it?
If you are simply incrementing the topmost value why not use something like
CREATE FUNCTION SystemNoShiva1
( -- Add the parameters for the function here
)
RETURNS int
declare @.st2 int
set @.st2 = (Select Max(SystemNo)+1 From TISSystemMst1)
insert into TISSystemMst1 values (@.st2)
return @.st2
Go
--Cursor Option As Follows--
declare @.sys01 int
Declare sys Cursor For
Select Max(SystemNo) From TISSystemMst1
Open sys
fetch next from sys
into @.sys01
WHILE @.@.fetch_status=0
begin
set @.sys01 = (@.sys01+1)
insert into TISSystemMst1 values(@.sys01)
fetch next from sys
into @.sys01
end
close sys
deallocate sys
return @.sys01
Hope this helps
|||Hello GDR,
Thx for u r prompt reply. I tried both samples but receive error as under.
Sample 1
While creating this function I receive message "Incorrect syntext near the keyword declare"
In Sample 2
In this code sample I receive same error message as I was receiving in my previous (main) message.
Nilkanth Desai
|||My humble appologies, the 'AS' is missing, I wrote the script on the forum page, not on a SQL interface thus parsing was not an option,
CREATE FUNCTION SystemNoShiva1
RETURNS int
AS
begin
declare @.st2 int
set @.st2 = (Select Max(SystemNo)+1 From TISSystemMst1)
insert into TISSystemMst1 values (@.st2)
return @.st2
End
