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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

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

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)


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
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)
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.
Dishan FernandoSoftware Engineer / DBACommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.