user defined function syntax error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

user defined function syntax error

Author

Topic

melvinra
Starting Member

2 Posts

Posted - 2008-09-17 : 07:58:33

im using a user defined function then when i execute a stored procedure calling that function it returns an error stating that ''A RETURN statement with a return value cannot be used in this context." how can i remedy this one?

here is the function

CREATE function GetNextDue(
@prm_due datetime,
@pymt_mode char(1))
returns datetime
as
begin
declare @next_due datetime

IF @PYMT_MODE = '1' --Annual
SET @next_due = DATEADD(YEAR, 1, @PRM_DUE)

IF @PYMT_MODE = '2' --Semi-Annual
SET @next_due = DATEADD(MONTH, 6, @PRM_DUE)

IF @PYMT_MODE = '3' --Quarterly
SET @next_due = DATEADD(MONTH, 3, @PRM_DUE)

IF @PYMT_MODE = '4' --Monthly
SET @next_due = DATEADD(MONTH, 1, @PRM_DUE)

return(@next_due)
end

the error says that incorrect syntax near function
must declare the variable '@pymt_mode'.
must declare the variable '@prm_due'.
.
.
.
.
''A RETURN statement with a return value cannot be used in this context."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 08:02:16

post your code. without seeing that its difficult to suggest something

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 08:17:10

its working fine for me anyways. Are you using some case sensitive collation?

melvinra
Starting Member

2 Posts

Posted - 2008-09-17 : 08:25:10

one week ago the our database became suspect then they detached and attach the database and it went back to its normal mode. then i tried to execute a procedure that calling a user defined function then that error appeared. why is that so?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 08:27:05

quote:
Originally posted by melvinra

one week ago the our database became suspect then they detached and attach the database and it went back to its normal mode. then i tried to execute a procedure that calling a user defined function then that error appeared. why is that so?

can you run query below and post back result obtained?

select case when 'test'='TEST' then 1 else 0 end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources