Showing posts with label Templates. Show all posts

SQL SERVER OBJECT TEMPLATES -- FUNCTION

Posted on Monday, 13 August 2012 by SQL Developer

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



/****************************************************************
*This script file creates the function FN_FUNCTION_NAME *
****************************************************************/
/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check if the function already exists

IF OBJECT_ID('dbo.FN_FUNCTION_NAME') IS NOT NULL
BEGIN-- If it exists, drop to recreate the function
DROP FUNCTION dbo.FN_FUNCTION_NAME
IF OBJECT_ID('dbo.FN_FUNCTION_NAME') IS NOT NULL
BEGINPRINT 'FUNCTION FN_FUNCTION_NAME DROPPED FAILED'
END
ELSE
BEGINPRINT 'FUNCTION FN_FUNCTION_NAME DROPPED SUCCESSFULLY'
END
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/
/********************************************************
** REVISION HISTORY : *
** ====================== *
** DATE VERSION NAME COMMENTS *
** ---------- ------- ----------------- -----------------*
** MM/DD/CCYY 1.0 Developer Name Initial Draft *
** *
*****************************************************************/
CREATE FUNCTION dbo.FN_FUNCTION_NAME(
/* Function parameters like
@pdtDate DATE
*/
)

RETURNS DATATYPE
-- Datatypes of output_parameter (Return parameter)
AS
BEGIN
/* Function Logic */
-- This will return the value output_parameter
RETURN output_parameter
END
GO
/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check for errors in creating the function
IF OBJECT_ID('dbo.FN_FUNCTION_NAME') IS NOT NULL
BEGIN
PRINT 'FUNCTION FN_FUNCTION_NAME CREATED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'FUNCTION FN_FUNCTION_NAME CREATION FAILED'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/


Posted in | Leave a comment

SQL SERVER OBJECT TEMPLATES -- INDEX CREATION

by SQL Developer

Template for creating index script in sql server:
--------------------------------------------------------
/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check if the index already exists
IF EXISTS ( SELECT 1
FROM sysindexes sysind
,sysobjects sysobj
WHERE sysind.name = 'IX_INDEX_NAME'
AND sysind.id = sysobj.id
AND sysobj.name = 'TBL_TABLE_NAME'
AND sysobj.uid = user_id('dbo')
AND sysobj.type = 'U')
-- P = Proc; V = View; U = Table; TR = Trigger

BEGIN
-- If it exists, drop to recreate the index on table TBL_TABLE_NAME
DROP INDEX dbo.TBL_TABLE_NAME.IX_INDEX_NAME
PRINT 'INDEX IX_INDEX_NAME OF TABLE TBL_TABLE_NAME DROPPED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'INDEX IX_INDEX_NAME OF TABLE TBL_TABLE_NAME DROPPED FAILED'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/
/********************************************************
** REVISION HISTORY : *
** ====================== *
** DATE                     VERSION           NAME                    COMMENTS *
** ---------- ------- ----------------- -----------------*
** MM/DD/CCYY          1.0               Developer Name           Initial Draft *
** *
*****************************************************************/
CREATE UNIQUE CLUSTERED INDEX IX_INDEX_NAME
ON TBL_TABLE_NAME
( COLUMN_NAME1

,COLUMN_NAME2
)

GO

/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check for errors in creating the index
IF EXISTS (SELECT 1
FROM sysindexes sysind
,sysobjects sysobj
WHERE sysind.name = 'IX_INDEX_NAME'
AND sysind.id = sysobj.id
AND sysobj.name = 'TBL_TABLE_NAME'
AND sysobj.uid = user_id('dbo')
AND sysobj.type = 'U')
-- P = Proc; V = View; U = Table; TR = Trigger
BEGIN
PRINT 'INDEX IX_INDEX_NAME ON TABLE TBL_TABLE_NAME CREATED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'INDEX IX_INDEX_NAME ON TABLE TBL_TABLE_NAME CREATION FAILED'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/

Posted in | Leave a comment

SQL SERVER OBJECT TEMPLATES -- INSERT SCRIPT

by SQL Developer


Template for creating a insert script  in sql server:
-----------------------------------------------------------

/*******************************************************
*This script file insert data into table TBL_TABLE_NAME*
********************************************************/
/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check for existence of if table
IF OBJECT_ID('dbo.TBL_TABLE_NAME') IS NOT NULL
BEGIN
-- Remove all the previous Data
TRUNCATE TABLE dbo.TBL_TABLE_NAME
PRINT 'TABLE TBL_TABLE_NAME TRUNCATED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'TABLE TBL_TABLE_NAME DOES NOT EXISTS'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/
/********************************************************
** REVISION HISTORY : *
** ====================== *
** DATE VERSION NAME COMMENTS *
** ---------- ------- ----------------- -----------------*
** MM/DD/CCYY 1.0 Developer Name Initial Draft *
** *
*****************************************************************/
/* Insert statements like*/
INSERT INTO dbo.TBL_TABLE_NAME
(COLUMN_NAME1,
COLUMN_NAME2
, ------/* remaining column namess*/
COLUMN_NAMEN)

VALUES
('VALUE1',
'VALUE2', ------/* remaining Values*/
'VALUEN')GO
-- SELECT * FROM dbo.TBL_TABLE_NAME

Posted in | Leave a comment

SQL SERVER OBJECT TEMPLATES -- STORED PROCEDURE

by SQL Developer

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
 

Posted in | Leave a comment

SQL SERVER OBJECT TEMPLATES -- VIEW

by SQL Developer

Template for creating a view in sql server is provided below:
--------------------------------------------------------------------------

/******************************************************************
======== BEGIN MAINTENANCE WRAPPER ==========
******************************************************************/
-- Check if the view already exists

IF EXISTS ( SELECT 1
FROM sysobjects
WHERE name = 'VW_VIEW_NAME'
AND uid = user_id('dbo')
AND type = 'V' )
-- P = Proc; V = View; U = Table; TR = Trigger
BEGIN

-- If it exists, drop to recreate the view

DROP VIEW dbo.VW_VIEW_NAME
PRINT 'VIEW VW_VIEW_NAME DROPPED SUCCESSFULLY'
END

ELSE
BEGIN
PRINT 'VIEW VW_VIEW_NAME DROPPED FAILED'
END

GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/
/********************************************************
** REVISION HISTORY : *
** ====================== *
** DATE VERSION NAME COMMENTS *
** ---------- ------- ----------------- -----------------*
** MM/DD/CCYY 1.0 Developer Name Initial Draft *
** *
*****************************************************************/
CREATE VIEW dbo.VW_VIEW_NAME(
)AS
/* view Body */
GO

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

IF EXISTS ( SELECT 1
FROM sysobjects
WHERE name = 'VW_VIEW_NAME'
AND uid = user_id('dbo')
AND type = 'V' )
-- P = Proc; V = View; U = Table; TR = Trigger
BEGIN
PRINT 'VIEW VW_VIEW_NAME CREATED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'VIEW VW_VIEW_NAME CREATION FAILED'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/
-- SELECT * FROM dbo.VW_VIEW_NAME

Posted in | Leave a comment

SQL SERVER OBJECT TEMPLATES -- TABLE

by SQL Developer

Sample template for creating a table in sql servercis given below:
-----------------------------------------------------------------------------

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

/* Table Body */

)
GO

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

IF EXISTS ( SELECT 1
FROM sysobjects
WHERE name = 'TBL_TABLE_NAME'
AND uid = user_id('dbo')
AND type = 'U')
-- P = Proc; V = View; U = Table; TR = Trigger
BEGIN

PRINT 'TABLE TBL_TABLE_NAME CREATED SUCCESSFULLY'
END
ELSE
BEGIN
PRINT 'TABLE TBL_TABLE_NAME CREATION FAILED'
END
GO
/******************************************************************
======== END MAINTENANCE WRAPPER ==========
******************************************************************/
-- SELECT * FROM dbo.TBL_TABLE_NAME

Posted in | Leave a comment