Solved

SQL Server - Stored Procedure to Return a value

Posted on 2003-10-28
11
2,814 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
  • 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:bhess1
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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 69

Expert Comment

by:ScottPletcher
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:
dishanf earned 500 total points
ID: 9647706
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now