how to add a function to sybase
I'm trying to add a function to sybase. I use sybase infrequently and only for running basic queries, simple updates, etc. I have good experience in Oracle and intermediate exp. in Sql Server.
I googled this and found a simple function but it didn't work, here's the sample function and then my results:
CREATE FUNCTION fullname (
firstname CHAR(30),
lastname CHAR(30) )
RETURNS CHAR(61)
BEGIN
DECLARE name CHAR(61);
SET name = firstname || ' ' || lastname;
RETURN (name);
END
===========
(10 rows affected)
1>
2> CREATE FUNCTION fullname (
3> firstname CHAR(30),
4> lastname CHAR(30) )
5> RETURNS CHAR(61)
6> BEGIN
7> DECLARE name CHAR(61);
8> SET name = firstname || ' ' || lastname;
9> RETURN (name);
10> END
11> go
Msg 156, Level 15, State 2:
Procedure 'fullname', Line 6:
Incorrect syntax near the keyword 'BEGIN'.
Msg 102, Level 15, State 1:
Procedure 'fullname', Line 7:
Incorrect syntax near 'CHAR'.
Msg 102, Level 15, State 1:
Procedure 'fullname', Line 9:
Incorrect syntax near ';'.
1>
I googled this and found a simple function but it didn't work, here's the sample function and then my results:
CREATE FUNCTION fullname (
firstname CHAR(30),
lastname CHAR(30) )
RETURNS CHAR(61)
BEGIN
DECLARE name CHAR(61);
SET name = firstname || ' ' || lastname;
RETURN (name);
END
===========
(10 rows affected)
1>
2> CREATE FUNCTION fullname (
3> firstname CHAR(30),
4> lastname CHAR(30) )
5> RETURNS CHAR(61)
6> BEGIN
7> DECLARE name CHAR(61);
8> SET name = firstname || ' ' || lastname;
9> RETURN (name);
10> END
11> go
Msg 156, Level 15, State 2:
Procedure 'fullname', Line 6:
Incorrect syntax near the keyword 'BEGIN'.
Msg 102, Level 15, State 1:
Procedure 'fullname', Line 7:
Incorrect syntax near 'CHAR'.
Msg 102, Level 15, State 1:
Procedure 'fullname', Line 9:
Incorrect syntax near ';'.
1>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
used your code, got a slightly different error message:
I changed it to PROCEDURE and got this error:
Msg 156, Level 15, State 2:
Procedure 'fullname', Line 8:
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1:
Procedure 'fullname', Line 10:
Incorrect syntax near 'CHAR'.
Msg 178, Level 15, State 1:
Procedure 'fullname', Line 12:
A RETURN statement with a return status may only be used in a SQL stored
procedure.I changed it to PROCEDURE and got this error:
Msg 102, Level 15, State 43:
Procedure 'fullname', Line 4:
Incorrect syntax near 'RETURNS'.
Msg 102, Level 15, State 1:
Procedure 'fullname', Line 7:
Incorrect syntax near 'CHAR'.
"PROCEDURE" isn't going to work, the syntax is different there.
Which Sybase product and version are you using please?
Which Sybase product and version are you using please?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh, good catch. :)
ASKER
just getting back to this, thank you Joe and Bret, checking now.
ASKER
Version is ASE version 12.5, I was told there aren't any functions.
I ran your code above and got this:
A RETURN statement with a return status may only be used in a SQL stored
procedure.
So I changed to PROCEDURE and got this:
Msg 156, Level 15, State 2:
Procedure 'fullname', Line 2:
Incorrect syntax near the keyword 'returns'.
Msg 137, Level 15, State 2:
Procedure 'fullname', Line 6:
Must declare variable '@lastname'.
So I got my basic answer, that I can create procedures, and now I have samples from the d.b. I'll close this out and open up other questions to do some more sybase things. Right now I'd like to create a procedure that
- retrieves a dataset via a cursor
- loop through the dataset and update each record using a simple encryption script
I ran your code above and got this:
A RETURN statement with a return status may only be used in a SQL stored
procedure.
So I changed to PROCEDURE and got this:
Msg 156, Level 15, State 2:
Procedure 'fullname', Line 2:
Incorrect syntax near the keyword 'returns'.
Msg 137, Level 15, State 2:
Procedure 'fullname', Line 6:
Must declare variable '@lastname'.
So I got my basic answer, that I can create procedures, and now I have samples from the d.b. I'll close this out and open up other questions to do some more sybase things. Right now I'd like to create a procedure that
- retrieves a dataset via a cursor
- loop through the dataset and update each record using a simple encryption script
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, thank you.
ASKER