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"?
- settings (SET NOCOUNT ON etc.)
- global settings (declare etc.))
- Begin Tray...
- Begin TRANSACTION
- custom error catching (raiserror etc.)
- actions...
- Commit Tran
- End Try
- Begin Catch
- Rollback Transaction
- 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.
SET XACT_ABORT ON;
to guarantee a rollback. UseTHROW
instead ofRAISERROR
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 onXACT_ABORT
to rollback when necessary. So only useCATCH
when you need to do something with the error, like logging. And in that case useTHROW;
to re-throw the error at the end.