Can''t create User Defined functions!

Question

Post reply

Can''t create User Defined functions!

jcfilth

SSC-Addicted

Points: 447
More actions
March 10, 2005 at 3:51 am

#164810

Hello,
I'm trying to create my first UDF. I just copied an example from the books online and tried to run it in the Enterprise Manager/SQL Analyzer and it didn't work!. the example is:
create function calc_interest ( @principal int , @rate numeric(10,5) , @years int )
returns int
as
begin
    declare @interest int
    set @interest = @principal * @rate * @years / 100
    RETURN(@interest)
end
The error I obtain is (translated from spanish):
Incorrect syntax near 'function'. You must declare the variable @principal. In this context you can't use the RETURN instruction as a returning value.
I have no idea of what is happening. I'm running this version: Microsoft SQL Server 2000 - 8.00.878 (Intel X86)   Nov 11 2003 13:37:42   Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: )
Any ideas? Can anyone help me?
Thanks, JC

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 1

I think this is a permission issue. check the user permission

My Blog: http://dineshasanka.spaces.live.com/

jcfilth SSC-Addicted Points: 447 More actions · Answer 2

The problem appears when I want to create the function, I can't create it, and I'm doing it as SA user.

JP de Jong-202059 SSCommitted Points: 1674 More actions · Answer 3

make sure it's the only command in your batch.

hth

JP

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Maybe I'm blind but I don't see anything wrong with the function. Have you tried this :

GO

create function calc_interest ( @principal int , @rate numeric(10,5) , @years int )

returns int

as

begin

declare @interest int

set @interest = @principal * @rate * @years / 100

RETURN(@interest)

end

GO

jcfilth SSC-Addicted Points: 447 More actions · Answer 5

It doesn't work. In fact, I tried to create the most simple UDF:

create function calc ( @i int)
returns int
as
RETURN(@i)
end

and it is still not working!

I'm sure I'm doing the things right, so I think the problem must be at my SQL Server version (8.00.878 -> post SP3a)

Anybody can confirm this?

Thanks to all

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

So you're logged as SA and you still can't create objects??

That's gonna be behond my knowledge. Good luck.

ron k SSCertifiable Points: 6103 More actions · Answer 7

Try prepending the owner to the function name example:

Create function dbo.CalcInterest( ...

jcfilth SSC-Addicted Points: 447 More actions · Answer 8

jcfilth

SSC-Addicted

Points: 447

March 10, 2005 at 9:44 am

#546418

Still not working.

Thanks for responding.

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 9

Can you see the User define functions from the Enterprise Manager

My Blog: http://dineshasanka.spaces.live.com/

jcfilth SSC-Addicted Points: 447 More actions · Answer 10

I don't have any UDF, so in Enterprise Manager I can't see any UDF...

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 11

eventhough u don't have udf u should have that link in EM

My Blog: http://dineshasanka.spaces.live.com/

jcfilth SSC-Addicted Points: 447 More actions · Answer 12

Yes, the link appears, and I can click it, showing an empty list of UDF's.I can also rigth-click it to access the option for creating a new one...

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 13

so u can create a function from there and not from the script

STRANGE!!!!

My Blog: http://dineshasanka.spaces.live.com/

jcfilth SSC-Addicted Points: 447 More actions · Answer 14

No no, I can open the window for creating a new one, but when I click 'check syntax' or try to save, the same error appears!