Create Function error msg - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Create Function error msg

Author

Topic

ramana_cr
Starting Member

14 Posts

Posted - 2002-01-09 : 14:23:04

use pubs
CREATE FUNCTION SalesByStore
(@ord_num varchar(30))
RETURNS int
AS
BEGIN
Declare @stor_id int
SELECT @stor_id = s.stor_id
FROM sales s
WHERE s.ord_num = @ord_num
return @stor_id
end

I took this example from SQLserver HELP and tried to compile it .. but I get the following message back..

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FUNCTION'.
Server: Msg 137, Level 15, State 1, Line 9
Must declare the variable '@ord_num'.
Server: Msg 178, Level 15, State 1, Line 10
A RETURN statement with a return status can only be used in a stored procedure.

Can you please let me know why...

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-09 : 14:54:54

Are you using SqlServer2000?

User Define Functions are only available in 2000.

This could explain the first error. Also Create Function, much like Create Procedure and Create View, will have to be submitted in its own batch (I think). I which case you would need a go after USE Pubs.

Edited by - Toddv on 01/09/2002 15:06:28

ramana_cr
Starting Member

14 Posts

Posted - 2002-01-09 : 15:05:27

yes.. It was recently upgraded from 7.0

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-09 : 15:10:04

quote:

yes.. It was recently upgraded from 7.0

Try adding the go after Use Pubs. Also check the database compatability Level to see that it is 8.0 not still 70

ramana_cr
Starting Member

14 Posts

Posted - 2002-01-09 : 15:25:35

The database compatiability lever is 70.. and I tried using 'go' after PUBS and it still doesnot work.. I am also not able to change the compatiabilty levels to 80 as I am getting an error back.. Do I have change for all the databases.. IF so will it affect other programs on the SQLSERVER database

ramana_cr
Starting Member

14 Posts

Posted - 2002-01-09 : 15:35:11

Toddv.. Thanks.. The SQLSERVER I was working was 7.0 not 2000. I was able to compile the function on 2000 .. Thanks

dave
Starting Member

15 Posts

Posted - 2002-01-09 : 18:03:30

I can't find our "Compatability Lever"!? Is it anywhere near the pressure gauge or closer to the server.crash button?

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-10 : 09:05:15

quote:

I can't find our "Compatability Lever"!? Is it anywhere near the pressure gauge or closer to the server.crash button?

You have to check behind the muffler bearings.

collegeguy
Starting Member

4 Posts

Posted - 2002-01-16 : 05:47:26

The option compatibility setting lies under the script tab. This tab will be available when you click on options in Query Analyzer.

Cheers!
C[8B]llege Guy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources