SQL Server - Stored Procedure to Return a value

I have a stored procedure as below. When a error occurs this is not returning me the value OF @@ERROR. I am not able to figure out the problem in this because it clearly returns a zero value when the insert succeeds. Additional Info, there are no return statements in the SP_logerror. That gets called and the record is inserted in the log table. When I try giving a PRINT statement before the return the value is properly getting printed.

Any suggestions or solutions will be of great help. Thanks in Advance.

Regards,
Ramesh

CREATE PROCEDURE dbo.SP_AuthCreateRoles
(
      @in_chvLoginId                  NAME,
      @in_intAdmin                  INT
)
DECLARE            @v_chvErrorDesc            VARCHAR(255),
            @v_chvAuditDesc            VARCHAR(255),
            @v_interror                  INT,
            @ErrorSave                  INT
BEGIN TRAN

IF (@in_intAdmin <> 0)
      BEGIN
            INSERT INTO tb_authloginrole (LoginId, RoleId, CreatedDt, CreatedBy)
                  VALUES (LTRIM(RTRIM(@in_chvLoginId)),
                        @in_intAdmin,
                        GETDATE(),
                        LTRIM(RTRIM(@in_chvUpdatedBy)))
            SELECT @v_interror = @@ERROR
            IF @v_interror <> 0
                  BEGIN
                        -- Error Log Entry
                        ROLLBACK TRAN
                        SET @v_chvErrorDesc = 'Error  Creating User Roles' + ' ' +@in_chvLoginId      
                        EXEC SP_LogError   @v_interror,  'SP_AuthCreateRoles', @v_chvErrorDesc, @in_chvUpdatedBy      
                        --PRINT @v_interror
                        SELECT @v_interror
                        RETURN
                  END
            ELSE
                                                BEGIN
                                                              COMMIT TRAN
                                                              SELECT 0
                                                              RETURN
                                       END            
END
brameshkannanAsked:
Who is Participating?
 
Dishan FernandoSoftware Engineer / DBACommented:
0
 
namasi_navaretnamCommented:
@in_chvUpdatedBy is not defined!!!

I think you are missing s pair of begin and end.

Try this!!!

CREATE PROCEDURE dbo.SP_AuthCreateRoles
(
     @in_chvLoginId               NAME,
     @in_intAdmin               INT
)
AS
BEGIN

DECLARE          @v_chvErrorDesc          VARCHAR(255),
          @v_chvAuditDesc          VARCHAR(255),
          @v_interror               INT,
          @ErrorSave               INT

BEGIN TRAN

IF (@in_intAdmin <> 0)
BEGIN

          INSERT INTO tb_authloginrole (LoginId, RoleId, CreatedDt, CreatedBy)
               VALUES (LTRIM(RTRIM(@in_chvLoginId)),
                    @in_intAdmin,
                    GETDATE(),
                    LTRIM(RTRIM(@in_chvUpdatedBy)))

          SELECT @v_interror = @@ERROR

          IF @v_interror <> 0
          BEGIN
                    -- Error Log Entry
                    ROLLBACK TRAN
                    SET @v_chvErrorDesc = 'Error  Creating User Roles' + ' ' +@in_chvLoginId    
                    EXEC SP_LogError   @v_interror,  'SP_AuthCreateRoles', @v_chvErrorDesc, @in_chvUpdatedBy    
                    --PRINT @v_interror
                    SELECT @v_interror
                    RETURN -1
          END
          ELSE
          BEGIN
                COMMIT TRAN
                SELECT 0
                RETURN 0
         END
END          
END
0
 
Brendt HessSenior DBACommented:
Looking at your code, one error (@in_intAdmin <> 0) will never be logged or return a value.  Is this your issue?
If so, try this:


CREATE PROCEDURE dbo.SP_AuthCreateRoles
(
     @in_chvLoginId               NAME,
     @in_intAdmin               INT
)
DECLARE          @v_chvErrorDesc          VARCHAR(255),
          @v_chvAuditDesc          VARCHAR(255),
          @v_interror               INT,
          @ErrorSave               INT
BEGIN TRAN

IF (@in_intAdmin <> 0)
     BEGIN
          INSERT INTO tb_authloginrole (LoginId, RoleId, CreatedDt, CreatedBy)
               VALUES (LTRIM(RTRIM(@in_chvLoginId)),
                    @in_intAdmin,
                    GETDATE(),
                    LTRIM(RTRIM(@in_chvUpdatedBy)))
          SELECT @v_interror = @@ERROR
          IF @v_interror <> 0
               BEGIN
                    -- Error Log Entry
                    ROLLBACK TRAN
                    SET @v_chvErrorDesc = 'Error  Creating User Roles' + ' ' +@in_chvLoginId    
                    EXEC SP_LogError   @v_interror,  'SP_AuthCreateRoles', @v_chvErrorDesc, @in_chvUpdatedBy    
                    --PRINT @v_interror
                    SELECT @v_interror
                    RETURN
               END
          ELSE
               BEGIN
                    COMMIT TRAN
                    SELECT 0
                    RETURN
               END          
     END
ELSE
     BEGIN
          ROLLBACK TRAN
          EXEC SP_LogError   -1,  'SP_AuthCreateRoles', 'Not Authorized', @in_chvUpdatedBy
          SELECT -1
          RETURN
     END
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
brameshkannanAuthor Commented:
Thanks a lot for your quick response.

I actually pasted a part of the stored proc. Undefined variables is not a problem. Actually, I have to insert a row to the table when @in_intAdmin <> 0
and if the insert fails, I need to rollback the transaction and write the error to a log table and return the error code. If the insert is successful I have to commit the transaction and return 0.

The successful insert is a happy path and it works just fine. When the insert fails, everything works, the transaction is rolledback, the error table record is written, but what ever I return, I am not getting it.

That is exactly this statement

SELECT @v_interror
RETURN

Actually when I give a PRINT statement and check, I get the value in @v_interror. I am wondering why it doesnt return the value stored in @v_interror

Pleaes advise

0
 
PaulBarbinCommented:
When you say its not returning anything, do you mean as a result?  In order to return a non rowset result, you need to use the syntax Return @v_interror

So you can then use the statement like this:

EXEC @result = dbo.SP_AuthCreateRoles


Paul
0
 
namasi_navaretnamCommented:
You will need to use RETURN statement

SELECT @v_interror = @@ERROR
IF @v_interror <> 0
BEGIN
   RETURN -1  // missing a this in your proc
END
ELSE
BEGIN
  RETURN 0 // missing this in your proc
END

ALSO at the end of sp add
RETRUN 0 // success

receive that return code using
DECLARE @rc int
EXEC @rc = dbo.SP_AuthCreateRoles

0
 
brameshkannanAuthor Commented:
Let me simplify the question. I am able to return values to my calling program in a general scenario.
lets take a small example as below

CREATE PROCEDURE dbo.SP_AuthCreateRoles
(
     @in_chvKey                   CHAR(1),
     @in_chvAdmin               VARCHAR(10)
)
SET NOCOUNT ON
BEGIN TRAN

IF (@in_chvKey <> '0')
     BEGIN
          INSERT INTO tb_authloginrole (tblkey, tbldesc)
               VALUES (@in_chvKey,@in_chvAdmin)
          IF @@ERROR <> 0
               BEGIN
                    ROLLBACK TRAN
                    SELECT 99
                    RETURN
               END
     END
COMMIT TRAN
SELECT 88
RETURN
GO

In the above stored proc, when the sql is successful, the value 88 is returned. When the statement is SQL for insert fails, it doesnt return the value 99.

I have tried using a variable and setting a value to the variable and returning it like

SET @v_interror = 99
RETURN @v_interror

I have also tried hardcoding the return value like

RETURN 99

Return doesnt work after the SQL fails.

I have tried that also. Doesnt work :((

Please Advise

Regards,
Ramesh

0
 
russellshomeCommented:
I think you are overcomplicating a simple insert statement.

You can achieve what you want with:
CREATE PROCEDURE dbo.SP_AuthCreateRoles
(
     @in_chvKey                   CHAR(1),
     @in_chvAdmin               VARCHAR(10)
)
INSERT INTO tb_authloginrole (tblkey, tbldesc)
  VALUES (@in_chvKey,@in_chvAdmin)


Use your application to make sure the procedure is not run when @in_chvKey = '0'

A transaction is useful when you are performing more than one operation. In this instance, there is only 1 insert so the transaction is of little value. If the insert fails, what is there to roll back?

Since there is only one error that can occur, all you need to do is trap this error in your code and call it 99 (or whatever you want). If there is no error then call that 88 (or whatever you want that is different to the number for the error).

If you have more than one error that can occur then use raiseerror
e.g. RAISERROR ('99', 16, 1)


0
 
DrYRMCommented:
Hello ,

It needs to be understood that if an error occurs while executing the proc then
@@ERROR varaible surely contains a value other than 0 hence one can track for this value and return a value back by using RAISERROR
eg:

if @@ERROR<>0
      begin
             RAISERROR('Error while inserting the record',@@ERROR,1)
             rollback transaction      
      end
0
 
russellshomeCommented:
1. Theres not a lot of point in raising an error that is already going to be raised. As stated, use RAISERROR when there is more than one place that an error can occur to determine where the error occurred

2. The second parameter of RAISERROR is severity. You really do not want to use @@ERROR as the severity.
Try RAISERROR('Error Location 1. Error Code:' + convert(varchar, @@ERROR, 16, 1)
0
 
Scott PletcherSenior DBACommented:
Is the error that is occuring trappable?  
That is, for certain errors you can capture the value of @@ERROR and go on processing (trappable); for others, the SP/batch ends immediately after the error occurs (nontrappable).
Add another INSERT after the first one that is SURE to succeed, then try again.  If the second INSERT doesn't occur, the error is probably untrappable and you will NOT be able to set a return value yourself in that situation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.