Exiting a stored procedure in sybase
I am getting some parameters in my stored procedure. Before working with these parameters, I want to test them, and if the parameters do not meet the requirements, I want to exit the stored procedure with an error message.
example code:
create proc abcd
(
zip varchar(20),
name varchar(20),
network varchar(1)
)
-- validation section
IF (len(zip)<>5 OR LEN(zip)<>9)
begin
print "The zip must be of 5 or 9 characters"
<---- how to exit from here--->
end
IF (len(name)<2)
begin
print "The name must be of at least 2 characters"
<---- how to exit from here--->
end
---- main code
How can I exit the procedure after getting the error above?
+3
source to share
2 answers
You can use the command return
as shown below
-- validation section
IF (len(zip)<>5 OR LEN(zip)<>9)
begin
print "The zip must be of 5 or 9 characters"
return 1
end
IF (len(name)<2)
begin
print "The name must be of at least 2 characters"
return 2
end
return 0 -- on the end of procedure
to catch the result, you can use this code:
declare @ProcResult int
execute @ProcResult = abcd @Zip = ..., @name...
select @ProcResult
+2
source to share
Let me suggest a few changes:
create procedure abcd
@zip varchar(20),
@name varchar(20),
@network varchar(1)
AS
-- validation section
IF (len(@zip)<>5 AND LEN(@zip)<>9)
begin
-- print "The zip must be of 5 or 9 characters"
raiserror 20000 "The zip must be of 5 or 9 characters"
end
IF (len(@name)<2)
begin
-- print "The name must be at least 2 characters"
raiserror 20001 "The name must be at least 2 characters"
end
There are tons of options with raiserror
, but that should point you in the right direction.
+1
source to share