Problem with function

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