Following things keep in mind while preparing database deployment script.
1) Include IF EXISTS THEN DROP Statement
2) Include IF NOT EXISTS THEN CREATE statement
In this way, we can avoid failure of deployment script during dpeloyment. the advantage of giving correct deployment script is deployment will be seamless otherwise we need to schedule deployment again. It may affect Production GO LIVE too. Similar scripts can be used for reverting the changes if something goes wrong after deployment. Below is the example of database script which is used for deploying database object.
/****** Object: StoredProcedure [dbo].[GetProductData] Script Date: 22/01/2023 15:32:56 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[GetProductData]')
AND type IN (
N'P'
,N'PC'
)
)
DROP PROCEDURE [dbo].[GetProductData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[GetProductData]')
AND type IN (
N'P'
,N'PC'
)
)
BEGIN
EXEC dbo.sp_executesql @statement =
N'
/*******************************************************
DESCRIPTION
This stored procedure is used to extract product data
CALLED FROM:
PRODUCT DATA MANAGEMENT PAGE
********************************************************
CHANGE HISTORY
********************************************************
Date: Author: Description: (CR#, Ver, Bug# etc)
----------- ----------- -------------------
********************************************************/
CREATE PROCEDURE [dbo].[GetProductData]
(
@ProductNumber NVARCHAR(10),
@Createdby INT,
@lReturnCode TINYINT = 0 OUTPUT, -- Success -0, Failure -1
@sMessage NVARCHAR(500) = NULL OUTPUT -- Success/Error Message to FrontEnd
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
SELECT
ProductCountry,
VD.ProductStatus,
VD.ProductOwner,
VD.IsWarranty,
VD.Cost,
VD.ServicetManager,
FROM ProductCategory VC (NOLOCK)
INNER JOIN ProductData VD (NOLOCK)
ON VC.ProductNumber = VD.ProductNumber
SELECT @lReturnCode = 0
END TRY
BEGIN CATCH
DECLARE @lErrorNumber INT,
@lErrorSeverity INT,
@lErrorState INT,
@lErrorLine INT,
@sErrorProcedure NVARCHAR(200),
@sErrorMessage NVARCHAR(4000);
--/* -- Assign variables to error-handling functions that
---- capture information for RAISERROR. */
SELECT @sErrorMessage = ERROR_MESSAGE(),
@sErrorProcedure = ISNULL(ERROR_PROCEDURE(), ''-''),
@lErrorNumber = ERROR_NUMBER(),
@lErrorSeverity = ERROR_SEVERITY(),
@lErrorState = ERROR_STATE(),
@lErrorLine = ERROR_LINE(),
@lReturnCode = 1
END CATCH
SET NOCOUNT OFF
END
'
END
GO
