Clever Geek Handbook

Try-Catch with Stored Procedures in SQL Server

I created a stored procedure with try-catch to delete records. The message should be displayed if the entry has been deleted or if there is an error. The uninstall procedure works, but the message is not displayed. Also, it asks me to enter a value for @msg and @return when executing the procedure.

Here is my stored procedure code:

IF OBJECT_ID('[dbo].[usp_AttendanceDelete]') IS NOT NULL
BEGIN 
    DROP PROCEDURE [dbo].[usp_AttendanceDelete] 
END 
GO
CREATE PROCEDURE [dbo].[usp_AttendanceDelete] 
@A_ID int,
@msg        VARCHAR(50) OUTPUT,
@return     INT     OUTPUT

AS 
BEGIN
    SET NOCOUNT ON 
    BEGIN TRANSACTION
        BEGIN TRY

    DELETE
    FROM   [dbo].[Attendance]
    WHERE  [A_ID] = @A_ID

                   SET @msg = 'Attendance Deleted'
                   SET @return = 1
        END TRY
        BEGIN CATCH
        --SELECT error_message() as error
            SET @msg = 'Attendance Delete FAIL.'
            SET @return = 0
            GOTO fail_rollback
        END CATCH
    COMMIT TRANSACTION
    RETURN
fail_rollback:
    ROLLBACK TRANSACTION
    RETURN
END
GO

Here is the code to complete the procedure:

EXECUTE [dbo].[usp_AttendanceDelete]
@A_ID ='234',
@msg='success', 
@return ='1'
+3


source to share


2 answers


Try this query below



IF OBJECT_ID('[dbo].[usp_AttendanceDelete]') IS NOT NULL
BEGIN 
    DROP PROCEDURE [dbo].[usp_AttendanceDelete] 
END 
GO
CREATE PROCEDURE [dbo].[usp_AttendanceDelete] 
@A_ID int,
@msg VARCHAR(100) OUTPUT,
@return INT    OUTPUT

AS
BEGIN TRANSACTION;
BEGIN TRY
    IF EXISTS(SELECT 1 FROM [Attendance] WHERE [A_ID] = @A_ID)
    Begin
    DELETE
    FROM   [dbo].[Attendance]
    WHERE  [A_ID] = @A_ID
    SELECT @msg = 'Attendance Deleted'
    SELECT @return = 1
    END
    ELSE
     Begin
    SELECT @msg='IN Put Record doesn''t exists'
    END
END TRY
BEGIN CATCH
    SELECT 
         ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;


    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

   DECLARE @msg VARCHAR(100),@return INt
   EXEC DBO.[usp_AttendanceDelete] 2,@msg OUT,@return OUT
   SELECT  @msg,  @return
+1


source


You can pass variables, not values, when calling a stored procedure, and you need to remember them as output

well:



DECLARE @msg varchar(50)
DECLARE @return int
EXECUTE [dbo].[usp_AttendanceDelete]
@A_ID ='234',
@msg=@msg output,
@return=@return output
--Do something with msg/return
+3


source






More articles:


All Articles