CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
TtT23
TtT23

Reputation: 7030

SQL Server stored procedure not returning a select result upon an error

I have a very simple SP that looks like the following:

BEGIN

SET @CUR_IO_GB = @IO_GB
SET @CUR_TOTDATE = @TOTDATE
SET @CUR_TOT_NO = @TOT_NO
SET @CUR_TOT_NO_SEQ = @TOT_NO_SEQ
SET @CUR_CUS_CD = @CUS_CD
SET @CUR_MAT_CD = @MAT_CD
SET @CUR_JCOLOR = @JCOLOR
SET @CUR_CANCEL_YN = @CANCEL_YN
SET @CUR_CREQTY = @CREQTY

SELECT  @ERR_MSG    = 2
PRINT 'INSERTING'
INSERT  INTO    LC_OUT_REQUEST_DETAIL(IO_GB, TOTDATE, TOT_NO, TOT_NO_SEQ, 
                LOCCD, ALLOC_QTY, OUT_QTY, IN_EMP, IN_DATE, UP_EMP, UP_DATE,
                CUS_CD, MAT_CD, JCOLOR, CANCEL_YN, DELIVERY_YN, OUT_YN, JDANGA)
VALUES('021', '20130817',  '000001', '000001', 
    'A0021', 2, 2, 'BATCH4', GETDATE(), 'BATCH4', GETDATE(),
    '0001031080', '41183', '1090', '1','1', 'N', 15 )

PRINT @ERR_MSG
SELECT  @ERR_MSG    = 2
RETURN @ERR_MSG

END

For testing purposes, I've intentionally tried to break the above stored procedure by inserting a duplicate key, causing a primary key violation.

My question is, I'm expecting a result of 2 in ERR_MSG, even when an error is raised (For example, Printing @ERR_MSG gives me 2, but select @ERR_MSG = 2 and then returning it always gives me null).

What is the cause for this behavior? Is there any way that I can still select and return the value desired upon an error?

Upvotes: 0

Views: 158

Answers (2)

Schalk
Schalk

Reputation: 142

Using the Error_Message() variable along with Try-Catch so that you know what the error is http://technet.microsoft.com/en-us/library/ms190358.aspx

SELECT @ERR_MSG = "NoError"  -- Indicating no error

BEGIN TRY
    INSERT  INTO    LC_OUT_REQUEST_DETAIL(IO_GB, TOTDATE, TOT_NO, TOT_NO_SEQ, 
            LOCCD, ALLOC_QTY, OUT_QTY, IN_EMP, IN_DATE, UP_EMP, UP_DATE,
            CUS_CD, MAT_CD, JCOLOR, CANCEL_YN, DELIVERY_YN, OUT_YN, JDANGA)
     VALUES('021', '20130817',  '000001', '000001', 
    'A0021', 2, 2, 'BATCH4', GETDATE(), 'BATCH4', GETDATE(),
    '0001031080', '41183', '1090', '1','1', 'N', 15 )
END TRY
BEGIN CATCH
    SELECT  @ERR_MSG = ERROR_MESSAGE()
END CATCH

SELECT @ERR_MSG

Upvotes: 2

ganders
ganders

Reputation: 7433

When encountering an error, the statement execution is stopped and only the error is returned. Add a try/catch block in there, then hard-code the error that you want to return (since you want it to be "2").

Upvotes: 1

Related Questions