SQL Creating functions

[fizch]

I have been working on creating user defined funcions in SQL 2000. Unfortunately, I am not having any luck. That is why I am here. I have this function:


CREATE FUNCTION fGetPartsUsed
( @EquipId as int,
@EndDate as datetime)
RETURNS TABLE
AS

RETURN select sum(p.cost * p.qty) as Parts
from fPartsUsed p
inner join fWorkOrder w
on(p.WorkOrderId = w.WorkOrderId)
where w.equipmentid = @EquipId
and workorderdate < @EndDate


The function will not compile and these are the errors that it is giving me:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FUNCTION'.
Server: Msg 137, Level 15, State 1, Line 11
Must declare the variable '@EquipId'.


I have been studying the SQL 2000 documentation for functions for the past couple of days, and I have yet to make this thing work. Please help. :cool:

[antares686]

Try putting Parenthesis around the selet statement.


CREATE FUNCTION fGetPartsUsed
( @EquipId as int,
@EndDate as datetime)
RETURNS TABLE
AS

RETURN (select sum(p.cost * p.qty) as Parts
from fPartsUsed p
inner join fWorkOrder w
on(p.WorkOrderId = w.WorkOrderId)
where w.equipmentid = @EquipId
and workorderdate < @EndDate)

[fizch]

Unfortunately, that did not work. I have tried using the samples that microsoft has in their help files (copied and pasted), but even those would not run. At this point, I think that the errors are going to be more difficult than just syntax.

[antares686]

I don't think your problem is the syntax at all. I just created one myself doing the exact same thing wihout issue. But I got curious about this part


Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FUNCTION'.


with that I believe you are not hitting a SQL 2000 server. I tried against one of my SQL 7 servers and that was exactly what I got along with need to declare the first variable it sees.

Run

SELECT @@version

against the server and make sure what server version you are running.

[fizch]

Well I upgraded the system from sql 6.5 to sql 2000 and this what the select statement returned:

Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)

But I agree, it should not be giving me a problem with the keyword function. My only concern is permissions. However, I am logged in as sa so I should have access to everything.

[antares686]

Ok I think I know now. Open Enterprise Manager and drill to the database in question. Right click the database in question and choose properties. On the Options tab you will find a section Compatibility with a level. I bet it is set to 6.5 you will have to change to 8.0 for functions to work. But make sure this won't cause you other issues in the process.

[fizch]

That worked! :D Thank you for all of your help. I knew that there was nothing wrong with the syntax.