Error and output variables in stored procedures

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • barmatt80
    New Member
    • Dec 2007
    • 55

    #1

    Error and output variables in stored procedures

    I have found alot of help on this site, and is helping me along. I am having trouble with a stored procedure. This is my first stored procedure.

    The task I was given was to create a stored procedure that will accept leave balances for employees from our web based leave system. Those 4 values are: @Annual_Forward , @Sick_Forward, @Family_Forward , @Other_Forward. All variables are not going to be passed, so allowing null/empty values is a necessity. That is no problem as I figured that part out. My problem is the Output parameters. I was told to return @SQLCode(This is the code returned by the SQL Server showing if successful or not.) and @ErrMsg(The message returned by the database when there is an error.)

    This is my stored procedure I wrote so far:
    Code:
    CREATE PROCEDURE [dbo].[usp_AcceptLeaveBalance]
    
    @Emp_SSN int,
    @Annual_Forward decimal(10,2),
    @Sick_Forward decimal(10,2),
    @Family_Forward decimal(10,2),
    @Other_Forward decimal(10,2)
    
    AS
    
    UPDATE OT_MAIN
    
    SET
    
    EmpAnnual_Forward = IsNull(@Annual_Forward, EmpAnnual_Forward),
    EmpSick_Forward = IsNull(@Sick_Forward, EmpSick_Forward),
    EmpFamily_Forward = IsNull(@Family_Forward, EmpFamily_Forward),
    EmpOther_Forward = IsNull(@Other_Forward, EmpOther_Forward)
    
    WHERE
    
    Emp_SSN=@Emp_SSN
    I can execute the procedure doing this:

    Code:
    exec usp_AcceptLeaveBalance 123456789, 10, null,null, null
    As you can tell it is very rudimentary. The exec statement works, but if a ssn is passed to the database that doesn't exist then obviously that should generate an error. So i think something using IF @@ROWCOUNT = 0 then show error.

    The @SQLCode and @ErrMsg is the problem. I am sure those two will be used to generate an email in the application side to alert me if there are errors. I hope some can help, and also post where to find some help on the web or books the would help a true beginner.

    Thanks.
  • ck9663
    Recognized Expert Specialist
    • Jun 2007
    • 2878

    #2
    Originally posted by barmatt80
    I have found alot of help on this site, and is helping me along. I am having trouble with a stored procedure. This is my first stored procedure.

    The task I was given was to create a stored procedure that will accept leave balances for employees from our web based leave system. Those 4 values are: @Annual_Forward , @Sick_Forward, @Family_Forward , @Other_Forward. All variables are not going to be passed, so allowing null/empty values is a necessity. That is no problem as I figured that part out. My problem is the Output parameters. I was told to return @SQLCode(This is the code returned by the SQL Server showing if successful or not.) and @ErrMsg(The message returned by the database when there is an error.)

    This is my stored procedure I wrote so far:
    Code:
    CREATE PROCEDURE [dbo].[usp_AcceptLeaveBalance]
    
    @Emp_SSN int,
    @Annual_Forward decimal(10,2),
    @Sick_Forward decimal(10,2),
    @Family_Forward decimal(10,2),
    @Other_Forward decimal(10,2)
    
    AS
    
    UPDATE OT_MAIN
    
    SET
    
    EmpAnnual_Forward = IsNull(@Annual_Forward, EmpAnnual_Forward),
    EmpSick_Forward = IsNull(@Sick_Forward, EmpSick_Forward),
    EmpFamily_Forward = IsNull(@Family_Forward, EmpFamily_Forward),
    EmpOther_Forward = IsNull(@Other_Forward, EmpOther_Forward)
    
    WHERE
    
    Emp_SSN=@Emp_SSN
    I can execute the procedure doing this:

    Code:
    exec usp_AcceptLeaveBalance 123456789, 10, null,null, null
    As you can tell it is very rudimentary. The exec statement works, but if a ssn is passed to the database that doesn't exist then obviously that should generate an error. So i think something using IF @@ROWCOUNT = 0 then show error.

    The @SQLCode and @ErrMsg is the problem. I am sure those two will be used to generate an email in the application side to alert me if there are errors. I hope some can help, and also post where to find some help on the web or books the would help a true beginner.

    Thanks.

    did not test this but i hope you'll get the idea:

    Code:
          CREATE PROCEDURE [dbo].[usp_AcceptLeaveBalance]
          @Emp_SSN int,
          @Annual_Forward decimal(10,2),
          @Sick_Forward decimal(10,2),
          @Family_Forward decimal(10,2),
          @Other_Forward decimal(10,2),
          @result varchar(50) OUTPUT
          AS
             declare @errornum int,  @rowupdated int
    
             UPDATE OT_MAIN
             SET
             EmpAnnual_Forward = IsNull(@Annual_Forward, EmpAnnual_Forward),
             EmpSick_Forward = IsNull(@Sick_Forward, EmpSick_Forward),
             EmpFamily_Forward = IsNull(@Family_Forward, EmpFamily_Forward),
             EmpOther_Forward = IsNull(@Other_Forward, EmpOther_Forward)
             WHERE
             Emp_SSN=@Emp_SSN
             
             select @errornum = @@error, @rowupdated = @@rowcount
           
             if @errornum <> 0 
                set result = 'Error updating table (' + cast(@errornum as varchar) + ')'
             else
                if @rowupdated > 0
                   set @result = 'Total records updated: ' + cast(@rowupdated as varchar)
                else
                   set @result = 'No records updated'
                
          go
    
    
          declare @resultofproc varchar
    
          exec usp_AcceptLeaveBalance 123456789, 10, null,null, null, @resultofproc OUTPUT
    here's a good reference

    you can also use 2 output parameters to pass both the errornumber and the message. you can get it from the sysmessages system table....

    also, am not sure if you pass an existing ssn and all the other values are the same, meaning what your passing and the existing value on the table are the same, am not sure if it will effect in the @@rowcount...

    happy coding...

    -- CK

    Comment

    Working...