TSQL Database Deployment script

 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