Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server - Stored Procedure to Return a value

Posted on 2003-10-28
11
Medium Priority
?
2,867 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:brameshkannan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +5
11 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9638003
@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
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 9638275
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
 

Author Comment

by:brameshkannan
ID: 9638516
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 9

Expert Comment

by:PaulBarbin
ID: 9638837
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9639109
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
 

Author Comment

by:brameshkannan
ID: 9639955
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
 
LVL 5

Expert Comment

by:russellshome
ID: 9640783
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
 

Expert Comment

by:DrYRM
ID: 9640870
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
 
LVL 5

Expert Comment

by:russellshome
ID: 9641219
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9642194
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
 
LVL 8

Accepted Solution

by:
Dishan Fernando earned 1500 total points
ID: 9647706
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question