Many time, we when we are creating or alter an existing stored procedure, it is good practice to add this logic.
There are many way to this. Some of the method are described here.
IF EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'MyStandardStoredProcedureTemplate')
AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.MyStandardStoredProcedureTemplate
See the cost plan for this
Another way of doing this is
IF OBJECTPROPERTY(object_id('dbo.My ProcedureName'), N'IsProcedure') = 1
DROP PROCEDURE dbo.My ProcedureName
GO
Cost plan for this method is
The best practice is to create your stored procedure is to create your stored procedure and then use to alter to make changes as shown below.
USE Database_Name;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.MyStandardStoredProcedureTemplate') IS NULL -- Check if SP Exists
EXEC('CREATE PROCEDURE dbo.MyStandardStoredProcedureTemplate AS SET NOCOUNT ON;') -- Create dummy/empty SP to for Alter Statement
GO
ALTER PROCEDURE dbo.MyStandardStoredProcedureTemplate -- Alter the SP Always
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
---Declare All your variables Here's
DECLARE @Now datetime = getdate()
-- Error variables
, @ErrorProc varchar(128)
, @ErrorMessage varchar(1000)
, @ErrorLine int
, @ErrorSeverity int
, @ErrorState int
-- If you are creating a temp table, must add a script to drop before it go to create a table. See the example bellow
IF OBJECT_ID(N'tempdb..#MyTempTableForDataProcessing') IS NOT NULL
BEGIN
DROP TABLE #MyTempTableForDataProcessing
END
-- Now Create your temp table here.
Create Table #MyTempTableForDataProcessing
(ID int
, FirstName varchar(20)
, MiddleName varchar(10)
, LastName varchar(20)
)
-- If you are using a subquery in your select, you can populate it during intial stage to load data in buffer for faster select
--Uncomment below statement to your needs
--Insert Into #MyTempTableForDataProcessing
--Select ID,FirstName,MiddleName,LastName from Some_Parent_table
BEGIN TRY
-- Populated all your temp or your Select queries here in begin try section
BEGIN TRANSACTION
-- Do an actual data write on physical tables like delete, insert, update etcs.
Print 'Hello'
COMMIT Transaction
END TRY
BEGIN CATCH
IF (XACT_STATE()) <> 0
ROLLBACK TRANSACTION;
select
@ErrorProc = ERROR_PROCEDURE()
, @ErrorMessage = ERROR_MESSAGE()
, @ErrorLine = ERROR_LINE()
, @ErrorSeverity = ERROR_SEVERITY()
, @ErrorState = ERROR_STATE()
RAISERROR (
@ErrorMessage
, @ErrorSeverity
, @ErrorState
);
END CATCH
END
