P
Patrick.O.Ige
Hi guys,
Is there any free stuff to generate DAL code out there?
Thanks in advance
Is there any free stuff to generate DAL code out there?
Thanks in advance
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
djmc
you checked out the enterprise library already? or do you want to generate
more code than what that saves you?
more code than what that saves you?
M
Mark Rae
Is there any free stuff to generate DAL code out there?
This is probably the best place to start...
http://msdn.microsoft.com/msdnmag/issues/05/07/DataPoints/
P
prabhupr
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/scripts/contributions/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/docs/dotnet/dotnetref/dotnetss.html#wp928843
--------------------------------------------------
Select ' private Int32 int' + BOBColumnName + ';'
Select ' private Int64 int' + BOBColumnName + ';'
Select ' private String str' + BOBColumnName + ';'
UNION
Select ' private DateTime dt' + BOBColumnName + ';'
Select ' private Boolean bit' + BOBColumnName + ';'
Select ' private Double dbl' + BOBColumnName + ';'
Select ' private Decimal dcm' + BOBColumnName + ';'
UNION
Select ' private Byte[] byt' + BOBColumnName + ';'
UNION
Select ' private Single sng' + BOBColumnName + ';'
--------------------------------------------------
-- 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'
Order By Name
*/
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/scripts/contributions/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/docs/dotnet/dotnetref/dotnetss.html#wp928843
--------------------------------------------------
Select ' private Int32 int' + BOBColumnName + ';'
UNIONFrom #t Where DataType In ( 'Int', 'TinyInt', 'smallint' )
Select ' private Int64 int' + BOBColumnName + ';'
UNIONFrom #t Where DataType In ( 'BigInt' )
Select ' private String str' + BOBColumnName + ';'
'text', 'ntext' )From #t Where DataType In ( 'char', 'varchar', 'nchar', 'nvarchar',
UNION
Select ' private DateTime dt' + BOBColumnName + ';'
UNIONFrom #t Where DataType In ( 'datetime', 'smalldatetime' )
Select ' private Boolean bit' + BOBColumnName + ';'
UNIONFrom #t Where DataType In ( 'bit' )
Select ' private Double dbl' + BOBColumnName + ';'
UNIONFrom #t Where DataType In ( 'float' )
Select ' private Decimal dcm' + BOBColumnName + ';'
'SmallMoney' )From #t Where DataType In ( 'Decimal', 'Money', 'Numeric',
UNION
Select ' private Byte[] byt' + BOBColumnName + ';'
'VarBinary' )From #t Where DataType In ( 'Binary', 'Image', 'TimeStamp',
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'
Where Type = 'u'From SysObjects
Order By Name
*/