CODEWITHSUNDEEP
sap-ase
Praveenks
Praveenks

Reputation: 1496

Exit from stored procedure in sybase

I am getting some parameters in my stored procedure. Before operating on these parameters, I want to validate them and if the parameters are not as per the requirement then I want to exit from the stored procedure with an error message.

sample 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 once I get the error as mentioned above?

Upvotes: 3

Views: 3747

Answers (2)

John
John

Reputation: 159

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"
          return 1
    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"
          return 2
    end

There are numerous options with raiserror but this should point you in the right direction.

Edit: added return statements

Upvotes: 1

Robert
Robert

Reputation: 25763

You can use return command as 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

Upvotes: 3

Related Questions