Hope this helps.
This template code generates sample C# Code OR SQL SP for Insert /
Delete / Update / Select, based on the input parameter you pass. The
first param value is SQL Table Name & 2nd param is the type of object
you are interested in creating
Steps + Assumption
===============
1. Ensure you have SQL table created in your DB
2. Create this SP in your DB
3. Execute this SP as
Exec GenerateStoredProcedureTemplate @TableName = '<YourTableName>',
@SPType = 'D'
go
Here are different possible values for 2nd param:
@SPType = 'B' -- Stored Procedure (Generate Business Object)
@SPType = 'I' -- Stored Procedure (Insert Data)
@SPType = 'U' -- Stored Procedure (Update Data)
@SPType = 'D' -- Stored Procedure (Delete Data)
@SPType = 'SA' -- Stored Procedure (SelectAll Data)
@SPType = 'SPK' -- Stored Procedure (SelectUsingPK Data)
Note: I have ensured that there are pointers to URL's in the SP from
where I have copied the original code & then extended it.
/************************************************** ****************************
* File: GenerateStoredProcedureTemplate.SQL
*------------------------------------------------------------------------------
* DATE AUTHOR DESCRIPTION
*------------------------------------------------------------------------------
* 19 Dec 2005 PP New
* 14 Apr 2006 PP Added Logic to Append LTRIM+RTRIM on Insert,
Update SP
Added the logic [Order By ColumnID]
Added Logic to geberate Business Object
File.
*------------------------------------------------------------------------------
************************************************** *****************************/
-------------------------
Set NOCOUNT ON
Set ARITHABORT OFF
Set Quoted_Identifier OFF
Set ANSI_WARNINGS OFF
-------------------------
If Exists ( Select * From sysobjects
Where id = object_id(N'[GenerateStoredProcedureTemplate]')
And OBJECTPROPERTY(id, N'IsProcedure') = 1 )
Begin
Print 'Stored Procedure [GenerateStoredProcedureTemplate]
Dropped Sucessfully!!!'
Drop Procedure [GenerateStoredProcedureTemplate]
End
GO
--------------------------------------------------------------------------------
/*
Exec GenerateStoredProcedureTemplate @TableName = 'Partner', @SPType
= 'B' -- Stored Procedure (Generate Business Object)
Exec GenerateStoredProcedureTemplate @TableName = 'Solution', @SPType
= 'I' -- Stored Procedure (Insert Data)
Exec GenerateStoredProcedureTemplate @TableName = 'Partner', @SPType
= 'U' -- Stored Procedure (Update Data)
Exec GenerateStoredProcedureTemplate @TableName = 'Partner', @SPType
= 'D' -- Stored Procedure (Delete Data)
Exec GenerateStoredProcedureTemplate @TableName = 'Partner', @SPType
= 'SA' -- Stored Procedure (SelectAll Data)
Exec GenerateStoredProcedureTemplate @TableName = 'Partner', @SPType
= 'SPK' -- Stored Procedure (SelectUsingPK Data)
*/
--------------------------------------------------------------------------------
-- TableStructure.sql
--
http://www.sqlservercentral.com/scri...utions/738.asp
-- Get the Table Structure using a simple query
----------------------------------------------------------------
CREATE Procedure GenerateStoredProcedureTemplate
@TableName Varchar(255)
,@SPType Varchar(5) -- I : Insert, U: Update, D : Delete, B :
Business Object
AS
Begin
/************************************************** ****************************
* File: GenerateStoredProcedureTemplate.sql
* PURPOSE: To Generate INSERT/UPDATE/DELETE SP template, based on
current schema
* DATE AUTHOR DESCRIPTION
*------------------------------------------------------------------------------
* 19 Dec 2005 PP Created
************************************************** *****************************/
--------------------------
SET NOCOUNT ON
SET ARITHABORT OFF
SET QUOTED_IDENTIFIER OFF
SET ANSI_WARNINGS OFF
--------------------------
--------------------------
-- Declare Variable(s)
--------------------------
Declare @SPName_Insert Varchar(255)
,@SPName_Update Varchar(255)
,@SPName_Delete Varchar(255)
,@SPName_SelectAll Varchar(255)
,@SPName_SelectUsingPK Varchar(255)
,@Action_Insert Varchar(255)
,@Action_Update Varchar(255)
,@Action_Delete Varchar(255)
,@Action_SelectAll Varchar(255)
,@Action_SelectUsingPK Varchar(255)
Declare @ColumnNameMAXLength Int
--------------------------
-- Clean INPUT Variable(s)
--------------------------
Select @TableName = LTRIM(RTRIM(@TableName))
Select @SPType = LTRIM(RTRIM(@SPType))
-----------------------------------
-- Verify Parameter Value Object(s)
-----------------------------------
--Select 'ObjectProperty : ' , ObjectProperty (
Object_ID(@TableName), 'IsTable')
If (ISNULL(ObjectProperty ( Object_ID(@TableName), 'IsTable'),0) !=
1)
Begin
RaisError('Parameter Value for @TableName is not a Valid
Table in this Database', 18,127)
Return -1
End
If (@SPType NOT IN ('I', 'U', 'D', 'B', 'SA', 'SPK'))
Begin
RaisError('Parameter Value for @SPType is not a Valid
value', 18,127)
Return -1
End
---------------------------------------
-- Assign/Initialize Variable(s) values
---------------------------------------
Select @Action_Insert = 'Insert'
,@Action_Update = 'Update'
,@Action_Delete = 'Delete'
,@Action_SelectAll = 'SelectAll'
,@Action_SelectUsingPK = 'SelectUsingPK'
Select @SPName_Insert = @TableName + '_' + @Action_Insert
,@SPName_Update = @TableName + '_' + @Action_Update
,@SPName_Delete = @TableName + '_' + @Action_Delete
,@SPName_SelectAll = @TableName + '_' +
@Action_SelectAll
,@SPName_SelectUsingPK = @TableName + '_' +
@Action_SelectUsingPK
-----------------------------------
-- Identify TABLE column attributes
-----------------------------------
-- Drop table #t
SELECT o.ID TableID,
c.ColID ColumnID,
o.name TableName,
c.name ColumnName,
c.name BOBColumnName,
t.name DataType,
c.Length ColumnLength,
CASE WHEN c.isnullable=1 THEN 'Yes'
ELSE 'No'
END
AS 'Nullable',
COLUMNPROPERTY (Object_ID(@TableName), c.name, 'IsIdentity'
) As 'IdentityColumn'
into #t
FROM sysobjects o
INNER JOIN syscolumns c ON (o.id=c.id and o.type='U' and o.name
not like 'dt%')
Inner Join systypes t ON (c.xtype=t.xtype)
--Where o.Name = @TableName
Where o.Name = @TableName --'Challenges'
Order BY c.colid
--------------------------------------------------
-- Delete columns which are for ADMIN purpose only
--------------------------------------------------
Delete #t where ColumnName IN ('CreatedDate', 'Status',
'UpdatedDate' )
Update #t Set TableName = LTRIM(RTRIM(TableName))
Update #t Set ColumnName = LTRIM(RTRIM(ColumnName))
Update #t Set BOBColumnName = LTRIM(RTRIM(BOBColumnName))
--Select * From #t
--sp_help #t
---------------------------------------
-- Assign/Initialize Variable(s) values
---------------------------------------
Select @ColumnNameMAXLength = MAX(DataLength(ColumnName))
From #t
Select @ColumnNameMAXLength = @ColumnNameMAXLength/2 --
handle nVarchar, so length will be twice
Select @ColumnNameMAXLength = @ColumnNameMAXLength + 5 --
Safety + Beautification
----------------------------------
-- Start [Business Object]
----------------------------------
IF @SPType = 'B'
Begin
Print 'using System;'
Print 'using System.Data;'
Print 'using System.Data.SqlClient;'
Print 'using System.Configuration;'
Print 'using System.Web;'
Print 'using System.Web.Security;'
Print 'using System.Web.UI;'
Print 'using System.Web.UI.WebControls;'
Print 'using System.Web.UI.WebControls.WebParts;'
Print 'using System.Web.UI.HtmlControls;'
Print ''
Print '/// <summary>'
Print '/// Summary description for ' + @TableName
Print '/// </summary>'
Print ''
Print 'public class Event : AlumniLibrary.BaseDataAccess'
Print '{'
Print ' AlumniLibrary.BaseDataAccess objDAL = new
AlumniLibrary.BaseDataAccess();'
Print ''
--------------------------------------------------
-- Replace Special Characters from BOBColumnNames
--------------------------------------------------
Update #t Set BOBColumnName = Replace(BOBColumnName,' ','')
Update #t Set BOBColumnName = Replace(BOBColumnName,'_','')
Update #t Set BOBColumnName = Replace(BOBColumnName,'-','')
--------------------------------------------------
-- Generate c# Code (Variables)
--
http://media.datadirect.com/download....html#wp928843
--------------------------------------------------
Select ' private Int32 int' + BOBColumnName + ';'
>From #t Where DataType In ( 'Int', 'TinyInt', 'smallint' )
UNION
Select ' private Int64 int' + BOBColumnName + ';'
>From #t Where DataType In ( 'BigInt' )
UNION
Select ' private String str' + BOBColumnName + ';'
>From #t Where DataType In ( 'char', 'varchar', 'nchar', 'nvarchar',
'text', 'ntext' )
UNION
Select ' private DateTime dt' + BOBColumnName + ';'
>From #t Where DataType In ( 'datetime', 'smalldatetime' )
UNION
Select ' private Boolean bit' + BOBColumnName + ';'
>From #t Where DataType In ( 'bit' )
UNION
Select ' private Double dbl' + BOBColumnName + ';'
>From #t Where DataType In ( 'float' )
UNION
Select ' private Decimal dcm' + BOBColumnName + ';'
>From #t Where DataType In ( 'Decimal', 'Money', 'Numeric',
'SmallMoney' )
UNION
Select ' private Byte[] byt' + BOBColumnName + ';'
>From #t Where DataType In ( 'Binary', 'Image', 'TimeStamp',
'VarBinary' )
UNION
Select ' private Single sng' + BOBColumnName + ';'
>From #t Where DataType In ( 'Real' )
--------------------------------------------------
-- Generate c# Code (Properties)
--------------------------------------------------
Select ' public Int32 ' + BOBColumnName
+ Char(10)
+ ' {'
+ Char(10)
+ ' get { return int' + BOBColumnName + ';
}' + Char(10)
+ ' set { int' + BOBColumnName + ' = value ;
}' + Char(10)
+ ' }'
From #t Where DataType In ( 'Int', 'TinyInt', 'smallint' )
UNION
Select ' public Int64 ' + BOBColumnName
+ Char(10)
+ ' {'
+ Char(10)
+ ' get { return int' + BOBColumnName + ';
}' + Char(10)
+ ' set { int' + BOBColumnName + ' = value ;
}' + Char(10)
+ ' }'
From #t Where DataType In ( 'BigInt' )
UNION
Select ' public String ' + BOBColumnName
+ Char(10)
+ ' {'
+ Char(10)
+ ' get { return str' + BOBColumnName + ';
}' + Char(10)
+ ' set { str' + BOBColumnName + ' = value ;
}' + Char(10)
+ ' }'
From #t Where DataType In ( 'char', 'varchar', 'nchar',
'nvarchar', 'text', 'ntext' )
UNION
Select ' public DateTime ' + BOBColumnName
+ Char(10)
+ ' {'
+ Char(10)
+ ' get { return dt' + BOBColumnName + ';
}' + Char(10)
+ ' set { dt' + BOBColumnName + ' = value ;
}' + Char(10)
+ ' }'
From #t Where DataType In ( 'datetime', 'smalldatetime' )
UNION
Select ' public Boolean ' + BOBColumnName
+ Char(10)
+ ' {'
+ Char(10)
+ ' get { return bit' + BOBColumnName + ';
}' + Char(10)
+ ' set { bit' + BOBColumnName + ' = value ;
}' + Char(10)
+ ' }'
From #t Where DataType In ( 'bit' )
UNION
Select ' public Double ' + BOBColumnName
+ Char(10)
+ ' {'
+ Char(10)
+ ' get { return dbl' + BOBColumnName + ';
}' + Char(10)
+ ' set { dbl' + BOBColumnName + ' = value ;
}' + Char(10)
+ ' }'
From #t Where DataType In ( 'float' )
UNION
Select ' public Decimal ' + BOBColumnName
+ Char(10)
+ ' {'
+ Char(10)
+ ' get { return dcm' + BOBColumnName + ';
}' + Char(10)
+ ' set { dcm' + BOBColumnName + ' = value ;
}' + Char(10)
+ ' }'
From #t Where DataType In ( 'Decimal', 'Money', 'Numeric',
'SmallMoney' )
UNION
Select ' public Byte[] ' + BOBColumnName
+ Char(10)
+ ' {'
+ Char(10)
+ ' get { return byt' + BOBColumnName + ';
}' + Char(10)
+ ' set { byt' + BOBColumnName + ' = value ;
}' + Char(10)
+ ' }'
From #t Where DataType In ( 'Binary', 'Image', 'TimeStamp',
'VarBinary' )
UNION
Select ' public Single ' + BOBColumnName
+ Char(10)
+ ' {'
+ Char(10)
+ ' get { return sng' + BOBColumnName + ';
}' + Char(10)
+ ' set { sng' + BOBColumnName + ' = value ;
}' + Char(10)
+ ' }'
From #t Where DataType In ( 'Real' )
--------------------------------------------------
-- Generate c# Code (Constructor)
--------------------------------------------------
Select ' public ' + @TableName + '()' +
Char(10)
+ ' {' +
Char(10)
+ ' // TODO: Add constructor logic here' +
Char(10)
+ ' }'
/*
name
--------
sql_variant
uniqueidentifier
*/
Print '}'
End
----------------------------------
-- Start [Insert Stored Procedure]
----------------------------------
IF @SPType = 'I'
Begin
--------------------------------------------
-- <SECTION: Object Existence Check + DROP >
--------------------------------------------
Print 'If Exists ( Select * From SysObjects '
Print ' Where ID = Object_ID(N''[' +
@SPName_Insert + ']'') '
Print ' And OBJECTPROPERTY(id,
N''IsProcedure'') = 1 )'
Print ' Begin'
Print ' Print ''Stored Procedure [' + @SPName_Insert
+ '] Dropped Sucessfully!!!'''
Print ' Drop Procedure [' + @SPName_Insert + ']'
Print ' End'
Print 'GO'
Print ''
-----------------------------------------
-- <SECTION: Object Creation -- Starting>
-----------------------------------------
Print 'CREATE Procedure ' + @SPName_Insert
--Print ' @TableName Varchar(255)'
Select ' @' + Replace(Replace(ColumnName,'
',''),'-','_') + Space(@ColumnNameMAXLength-(DataLength(ColumnName)/2))
+ ' ' + DataType
+ Case WHEN DataType = 'char' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'varchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'nchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'nvarchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
Else ''
End
From #t
Where IdentityColumn = 0 -- Insert Should never account
for IdentityColumn Column
And ColumnID = 2 -- First NON-Identity column
does not start with a PREFIX ","
--UNION
Select ' ,@' + Replace(Replace(ColumnName,'
',''),'-','_') + Space(@ColumnNameMAXLength-(DataLength(ColumnName)/2))
+ ' ' + DataType
+ Case WHEN DataType = 'char' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'varchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'nchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'nvarchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
Else ''
End
From #t
Where IdentityColumn = 0 -- Insert Should never account
for IdentityColumn Column
And ColumnID 2 -- First NON-Identity column
does not start with a PREFIX ","
Order By ColumnID
Print 'AS'
Print 'Begin'
Print '
/************************************************** ****************************'
Print ' * File : ' + @SPName_Insert
Print ' * PURPOSE : SP to insert/populate Table ' +
@TableName
Print ' * DATE AUTHOR DESCRIPTION'
Print '
*------------------------------------------------------------------------------'
Print ' * ' + Convert(Varchar(12),Getdate()) + ' ' +
Convert(Varchar(10),ISNULL(User_Name(),'dbo')) + ' Created'
Print '
************************************************** *****************************/'
Print ' ---------------------------'
Print ' SET NOCOUNT ON'
Print ' SET ARITHABORT OFF'
Print ' SET QUOTED_IDENTIFIER OFF'
Print ' SET ANSI_WARNINGS OFF'
Print ' ---------------------------'
--------------------------------------------
-- Added: 14 Apr 2006
-- SECTION: Trim ALL Char/Varchar Input Type
--------------------------------------------
Print ' -----------------------------------'
Print ' --Trim ALL Char/Varchar Input Type '
Print ' -----------------------------------'
Select ' Select @' + Replace(Replace(ColumnName,'
',''),'-','_') + Space(@ColumnNameMAXLength-(DataLength(ColumnName)/2))
+ ' = ' + 'LTRIM(RTRIM(ISNULL('
+ ' @' + Replace(Replace(ColumnName,' ',''),'-','_')
+ Space(@ColumnNameMAXLength-(DataLength(ColumnName)/2))
+ ' ,''''))) '
From #t
Where DataType IN
(
'char'
,'varchar'
,'nchar'
,'nvarchar'
)
Order By ColumnID
-------------------------------------------------------------------------
-- <SECTION: Object Creation -- Body of the SP (Identify
Column name(s))>
-------------------------------------------------------------------------
Print ' --------------------'
Print ' --Begin Transaction '
Print ' --------------------'
Print ' Begin Transaction'
Print ''
Print ' Insert INTO ' + @TableName
Print ' ('
Select ' ' + ' ' + '[' + ColumnName + ']'
From #t
Where IdentityColumn = 0 -- Insert Should never account
for IdentityColumn Column
And ColumnID = 2 -- First NON-Identity column
does not start with a PREFIX ","
--UNION
Select ' ' + ',' + '[' + ColumnName + ']'
From #t
Where IdentityColumn = 0 -- Insert Should never account
for IdentityColumn Column
And ColumnID 2
Order By ColumnID
---------------------------------------------------------------------------------------
-- <SECTION: Object Creation -- Body of the SP (Identify
respective Parameter name(s))>
---------------------------------------------------------------------------------------
Print ' )'
Print ' Select '
Select ' ' + ' @' + Replace(Replace(ColumnName,'
',''),'-','_')
From #t
Where IdentityColumn = 0 -- Insert Should never account
for IdentityColumn Column
And ColumnID = 2 -- First NON-Identity column
does not start with a PREFIX ","
--UNION
Select ' ' + ',@' + Replace(Replace(ColumnName,'
',''),'-','_')
From #t
Where IdentityColumn = 0 -- Insert Should never account
for IdentityColumn Column
And ColumnID 2
Order By ColumnID
Print ' IF @@Error != 0'
Print ' Begin'
Print ' ROLLBACK Transaction'
Print ' RaisError (''INSERT on Table ' +
@TableName + ' Failed!!!'',18,127)'
Print ' End'
Print ' Else'
Print ' Begin'
Print ' COMMIT Transaction'
Print ' End'
---------------------------------------
-- <SECTION: Object Creation -- Ending>
---------------------------------------
Print 'End'
Print 'go'
Print ''
Print 'IF @@Error = 0'
Print ' Begin'
Print ' Print ''Stored Procedure [' + @SPName_Insert
+ '] Created Sucessfully!!!'''
Print ' End'
Print 'Else'
Print ' Begin'
Print ' RaisError (''Stored Procedure [' +
@SPName_Insert + '] Creation Failed!!!'',18,127)'
Print ' End'
Print 'GO'
Print ''
End
----------------------------------
-- Start [Delete Stored Procedure]
----------------------------------
IF @SPType = 'D'
Begin
--------------------------------------------
-- <SECTION: Object Existence Check + DROP >
--------------------------------------------
Print 'If Exists ( Select * From SysObjects '
Print ' Where ID = Object_ID(N''[' +
@SPName_Delete + ']'') '
Print ' And OBJECTPROPERTY(id,
N''IsProcedure'') = 1 )'
Print ' Begin'
Print ' Print ''Stored Procedure [' + @SPName_Delete
+ '] Dropped Sucessfully!!!'''
Print ' Drop Procedure [' + @SPName_Delete + ']'
Print ' End'
Print 'GO'
Print ''
-----------------------------------------
-- <SECTION: Object Creation -- Starting>
-----------------------------------------
Print 'CREATE Procedure ' + @SPName_Delete
Select ' @' + Replace(Replace(ColumnName,'
',''),'-','_') + Space(@ColumnNameMAXLength-(DataLength(ColumnName)/2))
+ ' ' + DataType
+ Case WHEN DataType = 'char' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'varchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'nchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'nvarchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
Else ''
End
From #t
Where IdentityColumn = 1 -- Delete Should be performed
based on [Primary Key] Column
Print 'AS'
Print 'Begin'
Print '
/************************************************** ****************************'
Print ' * File : ' + @SPName_Delete
Print ' * PURPOSE : SP to DELETE Data from Table ' +
@TableName
Print ' * DATE AUTHOR DESCRIPTION'
Print '
*------------------------------------------------------------------------------'
Print ' * ' + Convert(Varchar(12),Getdate()) + ' ' +
Convert(Varchar(10),ISNULL(User_Name(),'dbo')) + ' Created'
Print '
************************************************** *****************************/'
Print ' ---------------------------'
Print ' SET NOCOUNT ON'
Print ' SET ARITHABORT OFF'
Print ' SET QUOTED_IDENTIFIER OFF'
Print ' SET ANSI_WARNINGS OFF'
Print ' ---------------------------'
-------------------------------------------------------------------------
-- <SECTION: Object Creation -- Body of the SP (Identify
Column name(s))>
-------------------------------------------------------------------------
Print ' --------------------'
Print ' --Begin Transaction '
Print ' --------------------'
Print ' Begin Transaction'
Print ''
Print ' Delete From ' + @TableName
Select ' Where ' + '[' + ColumnName + ']' + ' = ' + '@'
+ Replace(Replace(ColumnName,' ',''),'-','_')
From #t
Where IdentityColumn = 1
---------------------------------------------------------------------------------------
-- <SECTION: Object Creation -- Body of the SP (Identify
respective Parameter name(s))>
---------------------------------------------------------------------------------------
Print ' IF @@Error != 0'
Print ' Begin'
Print ' ROLLBACK Transaction'
Print ' RaisError (''DELETE from Table ' +
@TableName + ' Failed!!!'',18,127)'
Print ' End'
Print ' Else'
Print ' Begin'
Print ' COMMIT Transaction'
Print ' End'
---------------------------------------
-- <SECTION: Object Creation -- Ending>
---------------------------------------
Print 'End'
Print 'go'
Print ''
Print 'IF @@Error = 0'
Print ' Begin'
Print ' Print ''Stored Procedure [' + @SPName_Delete
+ '] Created Sucessfully!!!'''
Print ' End'
Print 'Else'
Print ' Begin'
Print ' RaisError (''Stored Procedure [' +
@SPName_Delete + '] Creation Failed!!!'',18,127)'
Print ' End'
Print 'GO'
Print ''
End
----------------------------------
-- Start [Update Stored Procedure]
----------------------------------
IF @SPType = 'U'
Begin
--------------------------------------------
-- <SECTION: Object Existence Check + DROP >
--------------------------------------------
Print 'If Exists ( Select * From SysObjects '
Print ' Where ID = Object_ID(N''[' +
@SPName_Update + ']'') '
Print ' And OBJECTPROPERTY(id,
N''IsProcedure'') = 1 )'
Print ' Begin'
Print ' Print ''Stored Procedure [' + @SPName_Update
+ '] Dropped Sucessfully!!!'''
Print ' Drop Procedure [' + @SPName_Update + ']'
Print ' End'
Print 'GO'
Print ''
-----------------------------------------
-- <SECTION: Object Creation -- Starting>
-----------------------------------------
Print 'CREATE Procedure ' + @SPName_Update
Select ' @' + Replace(Replace(ColumnName,'
',''),'-','_') + Space(@ColumnNameMAXLength-(DataLength(ColumnName)/2))
+ ' ' + DataType
+ Case WHEN DataType = 'char' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'varchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'nchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'nvarchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
Else ''
End
From #t
Where IdentityColumn != 0 -- Insert Should never account
for IdentityColumn Column
Select ' ,@' + Replace(Replace(ColumnName,'
',''),'-','_') + Space(@ColumnNameMAXLength-(DataLength(ColumnName)/2))
+ ' ' + DataType
+ Case WHEN DataType = 'char' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'varchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'nchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
WHEN DataType = 'nvarchar' Then '(' +
Convert(Varchar,ColumnLength) + ')'
Else ''
End
From #t
Where IdentityColumn = 0 -- Insert Should never account
for IdentityColumn Column
Print 'AS'
Print 'Begin'
Print '
/************************************************** ****************************'
Print ' * File : ' + @SPName_Update
Print ' * PURPOSE : SP to UPDATE Data in Table ' +
@TableName
Print ' * DATE AUTHOR DESCRIPTION'
Print '
*------------------------------------------------------------------------------'
Print ' * ' + Convert(Varchar(12),Getdate()) + ' ' +
Convert(Varchar(10),ISNULL(User_Name(),'dbo')) + ' Created'
Print '
************************************************** *****************************/'
Print ' ---------------------------'
Print ' SET NOCOUNT ON'
Print ' SET ARITHABORT OFF'
Print ' SET QUOTED_IDENTIFIER OFF'
Print ' SET ANSI_WARNINGS OFF'
Print ' ---------------------------'
--------------------------------------------
-- Added: 14 Apr 2006
-- SECTION: Trim ALL Char/Varchar Input Type
--------------------------------------------
Print ' -----------------------------------'
Print ' --Trim ALL Char/Varchar Input Type '
Print ' -----------------------------------'
Select ' Select @' + Replace(Replace(ColumnName,'
',''),'-','_') + Space(@ColumnNameMAXLength-(DataLength(ColumnName)/2))
+ ' = ' + 'LTRIM(RTRIM('
+ ' @' + Replace(Replace(ColumnName,' ',''),'-','_')
+ Space(@ColumnNameMAXLength-(DataLength(ColumnName)/2))
+ ' )) '
From #t
Where DataType IN
(
'char'
,'varchar'
,'nchar'
,'nvarchar'
)
Order By ColumnID
-------------------------------------------------------------------------
-- <SECTION: Object Creation -- Body of the SP (Identify
Column name(s))>
-------------------------------------------------------------------------
Print ' --------------------'
Print ' --Begin Transaction '
Print ' --------------------'
Print ' Begin Transaction'
Print ''
Print ' Update ' + @TableName
Print ' SET '
Select ' ' + '[' + ColumnName + ']' + ' = '
+
Space(@ColumnNameMAXLength-(DataLength(ColumnName)/2))
+ '@' +
Replace(Replace(ColumnName,' ',''),'-','_')
From #t
Where IdentityColumn = 0 -- Insert Should never account
for IdentityColumn Column
And ColumnID = 2 -- First NON-Identity column
does not start with a PREFIX ","
--UNION
Select ' ,' + '[' + ColumnName + ']' + ' = '
+
Space(@ColumnNameMAXLength-(DataLength(ColumnName)/2))
+ '@' +
Replace(Replace(ColumnName,' ',''),'-','_')
From #t
Where IdentityColumn = 0 -- Insert Should never account
for IdentityColumn Column
And ColumnID 2 -- First NON-Identity column
does not start with a PREFIX ","
Order By ColumnID
Select ' Where ' + '[' + ColumnName + ']' + ' = '
+
Space(@ColumnNameMAXLength-(DataLength(ColumnName)/2))
+ '@' +
Replace(Replace(ColumnName,' ',''),'-','_')
From #t
Where IdentityColumn = 1
---------------------------------------------------------------------------------------
-- <SECTION: Object Creation -- Body of the SP (Identify
respective Parameter name(s))>
---------------------------------------------------------------------------------------
Print ' IF @@Error != 0'
Print ' Begin'
Print ' ROLLBACK Transaction'
Print ' RaisError (''UPDATE On Table ' +
@TableName + ' Failed!!!'',18,127)'
Print ' End'
Print ' Else'
Print ' Begin'
Print ' COMMIT Transaction'
Print ' End'
---------------------------------------
-- <SECTION: Object Creation -- Ending>
---------------------------------------
Print 'End'
Print 'go'
Print ''
Print 'IF @@Error = 0'
Print ' Begin'
Print ' Print ''Stored Procedure [' + @SPName_Update
+ '] Created Sucessfully!!!'''
Print ' End'
Print 'Else'
Print ' Begin'
Print ' RaisError (''Stored Procedure [' +
@SPName_Update + '] Creation Failed!!!'',18,127)'
Print ' End'
Print 'GO'
Print ''
End
-------------------------------------
-- Start [SelectAll Stored Procedure]
-------------------------------------
IF @SPType = 'SA'
Begin
--------------------------------------------
-- <SECTION: Object Existence Check + DROP >
--------------------------------------------
Print 'If Exists ( Select * From SysObjects '
Print ' Where ID = Object_ID(N''[' +
@SPName_SelectAll + ']'') '
Print ' And OBJECTPROPERTY(id,
N''IsProcedure'') = 1 )'
Print ' Begin'
Print ' Print ''Stored Procedure [' +
@SPName_SelectAll + '] Dropped Sucessfully!!!'''
Print ' Drop Procedure [' + @SPName_SelectAll + ']'
Print ' End'
Print 'GO'
Print ''
-----------------------------------------
-- <SECTION: Object Creation -- Starting>
-----------------------------------------
Print 'CREATE Procedure ' + @SPName_SelectAll
Print 'AS'
Print 'Begin'
Print '
/************************************************** ****************************'
Print ' * File : ' + @SPName_SelectAll
Print ' * PURPOSE : SP to Select All Data from Table ' +
@TableName
Print ' * DATE AUTHOR DESCRIPTION'
Print '
*------------------------------------------------------------------------------'
Print ' * ' + Convert(Varchar(12),Getdate()) + ' ' +
Convert(Varchar(10),ISNULL(User_Name(),'dbo')) + ' Created'
Print '
************************************************** *****************************/'
Print ' ---------------------------'
Print ' SET NOCOUNT ON'
Print ' SET ARITHABORT OFF'
Print ' SET QUOTED_IDENTIFIER OFF'
Print ' SET ANSI_WARNINGS OFF'
Print ' ---------------------------'
-------------------------------------------------------------------------
-- <SECTION: Object Creation -- Body of the SP (Identify
Column name(s))>
-------------------------------------------------------------------------
Print ' ' + 'Select '
Select ' ' + ' ' + '[' + ColumnName + ']'
From #t
Where ColumnID = 1
--UNION
Select ' ' + ',' + '[' + ColumnName + ']'
From #t
Where ColumnID 1
Order By ColumnID
Print ' ' + 'From ' + @TableName
Print ' ' + 'Where Status = 1'
---------------------------------------
-- <SECTION: Object Creation -- Ending>
---------------------------------------
Print 'End'
Print 'go'
Print ''
Print 'IF @@Error = 0'
Print ' Begin'
Print ' Print ''Stored Procedure [' +
@SPName_SelectAll + '] Created Sucessfully!!!'''
Print ' End'
Print 'Else'
Print ' Begin'
Print ' RaisError (''Stored Procedure [' +
@SPName_SelectAll + '] Creation Failed!!!'',18,127)'
Print ' End'
Print 'GO'
Print ''
End
-----------------------------------------
-- Start [SelectUsingPK Stored Procedure]
-----------------------------------------
IF @SPType = 'SPK'
Begin
--------------------------------------------
-- <SECTION: Object Existence Check + DROP >
--------------------------------------------
Print 'If Exists ( Select * From SysObjects '
Print ' Where ID = Object_ID(N''[' +
@SPName_SelectUsingPK + ']'') '
Print ' And OBJECTPROPERTY(id,
N''IsProcedure'') = 1 )'
Print ' Begin'
Print ' Print ''Stored Procedure [' +
@SPName_SelectUsingPK + '] Dropped Sucessfully!!!'''
Print ' Drop Procedure [' + @SPName_SelectUsingPK +
']'
Print ' End'
Print 'GO'
Print ''
-----------------------------------------
-- <SECTION: Object Creation -- Starting>
-----------------------------------------
Print 'CREATE Procedure ' + @SPName_SelectUsingPK
Print 'AS'
Print 'Begin'
Print '
/************************************************** ****************************'
Print ' * File : ' + @SPName_SelectUsingPK
Print ' * PURPOSE : SP to Select All Data from Table ' +
@TableName
Print ' * DATE AUTHOR DESCRIPTION'
Print '
*------------------------------------------------------------------------------'
Print ' * ' + Convert(Varchar(12),Getdate()) + ' ' +
Convert(Varchar(10),ISNULL(User_Name(),'dbo')) + ' Created'
Print '
************************************************** *****************************/'
Print ' ---------------------------'
Print ' SET NOCOUNT ON'
Print ' SET ARITHABORT OFF'
Print ' SET QUOTED_IDENTIFIER OFF'
Print ' SET ANSI_WARNINGS OFF'
Print ' ---------------------------'
-------------------------------------------------------------------------
-- <SECTION: Object Creation -- Body of the SP (Identify
Column name(s))>
-------------------------------------------------------------------------
Print ' ' + 'Select '
Select ' ' + ' ' + '[' + ColumnName + ']'
From #t
Where ColumnID = 1
--UNION
Select ' ' + ',' + '[' + ColumnName + ']'
From #t
Where ColumnID 1
Order By ColumnID
Print ' ' + 'From ' + @TableName
Print ' ' + 'Where Status = 1'
Print ' ' + 'AND ' + @TableName + 'ID' + ' = ' +
'@' + @TableName + 'ID'
---------------------------------------
-- <SECTION: Object Creation -- Ending>
---------------------------------------
Print 'End'
Print 'go'
Print ''
Print 'IF @@Error = 0'
Print ' Begin'
Print ' Print ''Stored Procedure [' +
@SPName_SelectUsingPK + '] Created Sucessfully!!!'''
Print ' End'
Print 'Else'
Print ' Begin'
Print ' RaisError (''Stored Procedure [' +
@SPName_SelectUsingPK + '] Creation Failed!!!'',18,127)'
Print ' End'
Print 'GO'
Print ''
End
End
go
IF @@Error = 0
Begin
Print 'Stored Procedure [GenerateStoredProcedureTemplate]
Created Sucessfully!!!'
End
Else
Begin
RaisError ('Stored Procedure [GenerateStoredProcedureTemplate]
Creation Failed!!!',18,127)
End
GO
--Exec GenerateStoredProcedureTemplate @TableName = 'Challenges',
@SPType = 'D'
--go
/*
Select 'Exec GenerateStoredProcedureTemplate @TableName = ''' + Name +
''', @SPType = ''D''' + Char(10) + 'Go'
>From SysObjects
Where Type = 'u'
Order By Name
*/
