0

I want to make both error checks and insert and update in a single procedure.

There are triggers in the tables that I insert and update in the procedure, and other procedures work in these triggers.

Therefore, is it correct to use "transactions"?

  1. settings (SET NOCOUNT ON etc.)
  2. global settings (declare etc.))
  3. Begin Tray...
  4. Begin TRANSACTION
  5. custom error catching (raiserror etc.)
  6. actions...
  7. Commit Tran
  8. End Try
  9. Begin Catch
  10. Rollback Transaction
  11. End Catch

There may be many nested procedures due to triggers. Is it correct to use a structure like this?

create PROCEDURE myTableProcedure 
     @id int=0
    ,@dataField1 varchar(10) = '' 
    ,@dataField2 varchar(10) = ''
    ,@dataField3 varchar(10) = ''
    ,@dataField4 varchar(10) = ''
    ,@userId int=0
    ,@role tinyint=0
AS
BEGIN
--- 1) Settings
SET NOCOUNT ON
SET DATEFIRST 1

-- 2) Global definitions
declare @procedureCode varchar(20),@procedureMessage varchar(1000),@procedureId int

-- 3) Actions to be executed when there are no errors.
Begin Try

-- 4) Make sure the transactions complete without errors.
Begin TRANSACTION 

-- 5) Catch custom errors based on variables. If there is an error, send it to the "catch" section
----------------------------------------------------------------- Error Checking
    if (@role=0) 
    Begin 
        raiserror('Authority is undefined. Please specify the authorization value.',16,1) 
    End 

    else if ((select count(*) from customer where id=@userId and status=1)=0) 
    Begin 
        raiserror('No customer or Inactive state.',16,1) 
    End 
----------------------------------------------------------------- Error Checking/

-- 6) Do your actions.
----------------------------------------------------------------- Insert/Update Process
    --Insert
    if (@id=0)
    Begin
        insert into myTable (dataField1,dataField2,dataField3,dataField4,userId) values (@dataField1,@dataField2,@dataField3,@dataField4,@userId)
        set @procedureCode='0'
        set @procedureMessage='New record added'
        set @procedureId=SCOPE_IDENTITY()
        select @procedureCode as 'procedureCode',@procedureMessage as 'procedureMessage',@procedureId as 'procedureId'
    End 
    --Insert/

    --Updated
    else if (@id>0)
    begin
        update myTable set
             dataField1=@dataField1
            ,dataField2=@dataField2
            ,dataField3=@dataField3
            ,dataField4=@dataField4
            ,recTime=getdate()
        where id=@id

    set @procedureCode='0'
    set @procedureMessage='The record has been changed.'
    set @procedureId=isnull(@id,0)
    select @procedureCode as 'procedureCode',@procedureMessage as 'procedureMessage',@procedureId as 'procedureId'
    end
    --Updated/

-- 7) Complete Actions
Commit Transaction

-- 8) Close Try
End Try
----------------------------------------------------------------- Insert/Update Process






-- 9) Error handling section
----------------------------------------------------------------- If an error occurs
Begin Catch
-- 10) Undo if there is an error.
Rollback Transaction

    DECLARE @ERROR_MESSAGE_USER NVARCHAR(2048) =
    (CASE ERROR_NUMBER()
            WHEN 547 THEN concat('The changes you requested could not be made due to some restrictions. Error Content:',ERROR_MESSAGE())
            WHEN 229 THEN concat('The changes you requested could not be made due to some restrictions. Error Content:',ERROR_MESSAGE())
            ELSE ERROR_MESSAGE()
    END)

    set @procedureCode=ERROR_NUMBER()
    set @procedureMessage=CONCAT(@ERROR_MESSAGE_USER, CHAR(13),'Error Row:', ERROR_LINE(), CHAR(13), 'Error Nr:', ERROR_NUMBER(), CHAR(13), 'Error Function:', ERROR_PROCEDURE(), CHAR(13), 'Error Severity:', ERROR_SEVERITY(), CHAR(13), 'Error Status:', ERROR_STATE(), CHAR(13))
    set @procedureId=isnull(@id,0)

    select @procedureCode as 'procedureCode',@procedureMessage as 'procedureMessage',@procedureId as 'procedureId'

-- 10) Error handling finished.
End Catch
----------------------------------------------------------------- If an error occurs/


END



It looks flawless but I have doubts about performance and whether it's the right coding.

enter image description here

4
  • This is a bit broad - what issue in particular are you encountering and what problem are you trying to solve?
    – user212533
    Oct 25, 2022 at 17:58
  • 1
    It’s a long read, but it will answers all of your questions: Error and Transaction Handling in SQL Server Oct 25, 2022 at 22:00
  • Your question seems to lack focus. What exactlly are you asking? Oct 26, 2022 at 18:56
  • Issues I can se with the above code: Use SET XACT_ABORT ON; to guarantee a rollback. Use THROW instead of RAISERROR because it is handled better. Do not use that bogus "error handling", it does nothing to help with actually handling the error, and is likely to confuse client drivers and prevent the error from bubling back to the client. Instead just rely on XACT_ABORT to rollback when necessary. So only use CATCH when you need to do something with the error, like logging. And in that case use THROW; to re-throw the error at the end. Oct 26, 2022 at 19:01

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.