SQL SERVER OBJECT TEMPLATES -- STORED PROCEDURE

Template for creating a stored procedure in sql server is given below:
-----------------------------------------------------------------------------------



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
GO
/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check if the procedure already exists

IF EXISTS ( SELECT 1
FROM sysobjects
WHERE name = 'USP_PROCEDURE_NAME'
AND uid = user_id('dbo')
AND type = 'P' )
-- P = Proc; V = View; U = Table; TR = Trigger
BEGIN-- If it exists, drop to recreate the procedure
DROP PROCEDURE dbo.USP_PROCEDURE_NAME
PRINT 'PROCEDURE USP_PROCEDURE_NAME DROPPED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'PROCEDURE USP_PROCEDURE_NAME DROPPED FAILED'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/
/********************************************************
** REVISION HISTORY : *
** ====================== *
** DATE VERSION NAME COMMENTS *
** ---------- ------- ----------------- -----------------*
** MM/DD/CCYY 1.0 Developer Name Initial Draft *
** *
*****************************************************************/

CREATE PROCEDURE dbo.USP_PROCEDURE_NAME
AS
BEGIN
/* Declare Local Variables */
/* Initialize local variables */
/* Procedure Core Logic */
END
-- End of Procedure

GO

/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check for errors in creating the Procedure

IF EXISTS ( SELECT 1
FROM sysobjects
WHERE name = 'USP_PROCEDURE_NAME'
AND uid = user_id('dbo')
AND type = 'P' )
-- P = Proc; V = View; U = Table; TR = Trigger
BEGIN
PRINT 'PROCEDURE USP_PROCEDURE_NAME CREATED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'PROCEDURE USP_PROCEDURE_NAME CREATION FAILED'
END

GO

/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/
SET QUOTED_IDENTIFIER OFF

GO
SET ANSI_NULLS OFF
GO

SET NOCOUNT OFF

GO

-- EXECUTE dbo.USP_PROCEDURE_NAME
 

This entry was posted in . Bookmark the permalink.

Leave a reply