CODEWITHSUNDEEP
sql-serverstored-procedures
Guyute
Guyute

Reputation: 149

what is the Correct way to use try catch in a stored procedure

I am creating a bunch of stored procedures for add, update and delete in a database I am doing ass a project for school. I want to use try catch and a flag to tell the user if there was success or not. I am using SQL server 2005, I have the basic stored procedures down but want to add this to make my project better. I keep getting a SQL error improper syntax near keyword set. Here is my code:

CREATE PROCEDURE dbo.addCustomer 
-- Add the parameters for the stored procedure here
@flag bit output, --return 1 for success and 0 for fail
@Customer_ID varchar(50),
@sales_region varchar(50),
@Items_Recieved varchar(max),
@Hual_ID varchar(50)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
INSERT into dbo.Customer(Customer_ID,Sales_region, Items_Recieved, Hual_ID)
values(@Customer_ID,@sales_region, @Items_Recieved, @Hual_ID  
set @flag=1;
IF @@TRANCOUNT>0
BEGIN commit TRANSACTION;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
BEGIN rollback TRANSACTION;
END

set @flag=0;
END CATCH
END

GO

Can anybody point me in the right direction, I am sure I did something simple wrong, or forgot to do something. Thanks.

Upvotes: 1

Views: 1579

Answers (1)

M.Ali
M.Ali

Reputation: 69524

CREATE PROCEDURE dbo.addCustomer 
-- Add the parameters for the stored procedure here
@flag bit output, --return 1 for success and 0 for fail
@Customer_ID varchar(50),
@sales_region varchar(50),
@Items_Recieved varchar(max),
@Hual_ID varchar(50)
AS
BEGIN
     SET NOCOUNT ON;               
 BEGIN TRY

    BEGIN TRANSACTION
        INSERT INTO dbo.Customer(Customer_ID,Sales_region, Items_Recieved, Hual_ID)
        VALUES(@Customer_ID,@sales_region, @Items_Recieved, @Hual_ID)  
    COMMIT TRANSACTION;

       SET @flag=1;
  END TRY

  BEGIN CATCH
      IF (@@TRANCOUNT > 0)
       BEGIN 
         ROLLBACK TRANSACTION;
       END

    SET @flag=0; 
     --  Utilise your other Error Functions to get detailed 
     -- Information about the Error, function like 
     --  ERROR_LINE(), ERROR_MESSAGE(), ERROR_NUMBER()....
  END CATCH

END
GO

Upvotes: 2

Related Questions