AUTOGENERATE INSERT UPDATE SELECT STATEMENTS UPDATED
First execute 'script 1'
Then execute script 2'
Once the stored procedure _SPLOG_SPConstructor has been made execute it with the default parameters.
These stored procedures generate common and very simple stored procedures. There is commented text in the 2nd stored procedure that allows u to observe usage statistics that can be used to remove the unused stored procedures once development is finished. For example you would remove all stored procedures with a usage of 0.
This code has been tested on smallish databases with a reasonable number of columns. it works and saves quite a lot of time.
there are a few known bugs:
If your table names do not conform to normal naming conventions (i.e. have spaces in the table names) you might have difficulty.
Also the code that is generated has to fit into 8000 characters.
Also it does not support timestamp datatypes - and there could be others. it does support the common datatypes. Since its open source go ahead and change it. Just let me know please.
For all reasonable designs this will work as planned.
Use Testmode = 1 with CreateLog = 1 to generate the code but not to execute it.
Purpose:
The purpose of the script is to create all the 'logic' free sp's one is likely to use in a new application from a 'virgin' database.
It creates: selectbyid, selectall, insert, update stored procedures for every table in the database.
By virgin database I mean a normalized database with standard naming conventions (i.e. not spaces in table names or column names), with no stored procedures.
Installation:
Option 1: if you develop databases from scratch often I would recommend running this on the model database and then every database you create then on will include these two procs.
Option 2: run the create procs on an individual database.
Execution:
Once the sprocs are installed just run spconstructor, with default parameters or testmode = 1.
Advantage:
This saves a large amount of routine and 'boring' programming, as well as the time it typically takes to do it.
Disadvantage:
You may end up with redundant sprocs in you db. If you modify the code (uncomment) it allows you to keep a log of stored procedure usage during development, once the development is over just remove all the stored procedures with a usage of 0.
Limitations:
Depending on your coding style you may prefer to have you insert and updates in one proc. in this case you need to modify the open source.
Otherwise have fun!
These scripts use the metadata from you database to create 4 statements for each table, for example:
selectbyid, selectAll, update, insert
-- =============================================
-- AUTHOR: Chris Morton
-- CREATED DATE: 2008/10/23
-- AUTOGENERATED BY SQL CODE GENERATION TOOL VERSION 1.1 BETA - NO LIABILITY CAN BE CLAIMED FROM CHRIS MORTON - CONTACT cbmorton@gmail.com
-- =============================================
CREATE PROCEDURE voselectFeedbackCategoryByID
@FeedbackCategoryID BIGINT
--@FeedbackCategoryName varchar(50)
AS
BEGIN
SELECT [FeedbackCategoryID],
[FeedbackCategoryName]
FROM [FeedbackCategory]
WHERE FeedbackCategoryID = @FeedbackCategoryID
END
-- =============================================
-- AUTHOR: Chris Morton
-- CREATED DATE: 2008/10/23
-- AUTOGENERATED BY SQL CODE GENERATION TOOL VERSION 1.1 BETA - NO LIABILITY CAN BE CLAIMED FROM CHRIS MORTON - CONTACT cbmorton@gmail.com
-- =============================================
CREATE PROCEDURE voselectALLFeedbackCategory
--@FeedbackCategoryID bigint
--@FeedbackCategoryName varchar(50)
AS
BEGIN
SELECT [FeedbackCategoryID],
[FeedbackCategoryName]
FROM [FeedbackCategory]
END
-- =============================================
-- AUTHOR: Chris Morton
-- CREATED DATE: 2008/10/23
-- AUTOGENERATED BY SQL CODE GENERATION TOOL VERSION 1.1 BETA - NO LIABILITY CAN BE CLAIMED FROM CHRIS MORTON - CONTACT cbmorton@gmail.com
-- =============================================
CREATE PROCEDURE voinsertFeedbackCategory
@FeedbackCategoryID BIGINT,
@FeedbackCategoryName VARCHAR(50)
AS
BEGIN
INSERT INTO FeedbackCategory
(
[FeedbackCategoryID],
[FeedbackCategoryName]
)
VALUES (
@FeedbackCategoryID,
@FeedbackCategoryName
)
END
-- =============================================
-- AUTHOR: Chris Morton
-- CREATED DATE: 2008/10/23
-- AUTOGENERATED BY SQL CODE GENERATION TOOL VERSION 1.1 BETA - NO LIABILITY CAN BE CLAIMED FROM CHRIS MORTON - CONTACT cbmorton@gmail.com
-- =============================================
CREATE PROCEDURE voupdateFeedbackCategory
@FeedbackCategoryID BIGINT,
@FeedbackCategoryName VARCHAR(50)
AS
BEGIN
UPDATE FeedbackCategory
SET FeedbackCategoryName = @FeedbackCategoryName
WHERE FeedbackCategoryID = @FeedbackCategoryID
END
--Script 1
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
-- =============================================
--PART OF THE MACRO CODE EXECUTION BY CHRIS MORTON
--CONTACT CBMORTON@GMAIL.COM
--NO LIABLITITY CAN BE CLAIMED
--VERSION 1.1 BETA OCTOBER 23 2008
--VERSION 1.2 BETA FEBRUARY 16 2009
--cm ADDED SUPPORT FOR COLUMN NAMES AND TABLE NAMES WITH SPACES ETC
--cm ADDED AN OUTPUT PARAMETER TO INSERT STATEMENTS
--cm ADDED MORE INFORMATIVE ERROR HANDLING
-- =============================================
ALTER PROCEDURE [dbo].[_SPLOG_AutoGenStatements]
@Tablename VARCHAR(50),
@AuthorName VARCHAR(50) = 'Chris Morton',
@Prefix VARCHAR(3) = NULL,
@CreateLog BIT,
@TestMode BIT
AS
BEGIN
DECLARE @PrimaryKey VARCHAR(50)
DECLARE @ColumnParameter VARCHAR(4000)
DECLARE @CreatedDate VARCHAR(50)
SET @CreatedDate = CONVERT(VARCHAR(50), GETDATE(), 111)
DECLARE @SPName VARCHAR(100)
DECLARE @SPHeader VARCHAR(200)
DECLARE @SPDescription VARCHAR(1000)
SET @SPDescription = '-- ============================================='
+ CHAR(10) + CHAR(13) + '-- AUTHOR: ' + @AuthorName + CHAR(10)
+ CHAR(13) + '-- CREATED DATE: ' + @CreatedDate + CHAR(10)
+ CHAR(13)
+ '-- AUTOGENERATED BY SQL CODE GENERATION TOOL VERSION 1.2 BETA - NO LIABILITY CAN BE CLAIMED FROM CHRIS MORTON - CONTACT cbmorton@gmail.com'
+ CHAR(10) + CHAR(13)
+ '-- =============================================' + CHAR(10)
+ CHAR(13)
DECLARE @Statement VARCHAR(4000)
DECLARE @SPText VARCHAR(8000)
DECLARE @ParameterName VARCHAR(100)
DECLARE @ParameterDataType VARCHAR(50)
DECLARE @ParameterList VARCHAR(2000)
DECLARE @WhereString VARCHAR(300)
DECLARE @PK VARCHAR(50)
SET @WhereString = CHAR(10) + CHAR(13) + 'WHERE '
DECLARE @SelectStatement VARCHAR(4000)
SET @SelectStatement = 'SELECT'
DECLARE @InsertStatement VARCHAR(4000)
SET @InsertStatement = 'INSERT INTO'
DECLARE @InsertStatementValues VARCHAR(2000)
DECLARE @UsageUpdateStatement VARCHAR(150)
SET @UsageUpdateStatement = ''
DECLARE @TableMetaData AS TABLE
(
ID INT IDENTITY(1, 1),
ColumnName VARCHAR(50) NOT NULL,
DataType VARCHAR(50) NOT NULL,
Length VARCHAR(4) NULL,
isPK BIT NULL
)
INSERT INTO @TableMetaData
(
ColumnName,
DataType,
Length,
isPK
)
SELECT a.Column_name AS columnname,
Data_type AS datatype,
Character_maximum_length AS length,
CASE b.table_name
WHEN b.table_name THEN 1
ELSE 0
END AS ispk
FROM INFORMATION_SCHEMA.columns a
LEFT JOIN INFORMATION_SCHEMA.key_column_usage b ON a.Column_name = b.Column_name
AND a.table_name = b.table_name
AND b.constraint_Name LIKE 'PK%'
WHERE a.table_name = @Tablename
DECLARE @NumColumns INT
SET @NumColumns = ( SELECT COUNT(1)
FROM @TableMetaData
)
DECLARE @ColumnName VARCHAR(50)
DECLARE @Counter INT
--selectByID statement
-- SET @UsageUpdateStatement = CHAR(10) + CHAR(13)
-- + 'UPDATE [SPLOG] SET [USAGE] = [USAGE] + 1 WHERE SPNAME = '''
SET @ParameterList = ''
SET @ParameterName = ''
SET @SelectStatement = 'SELECT'
SET @PK = ( SELECT ColumnName
FROM @TableMetaData
WHERE ispk = 1
)
SET @Counter = 1
WHILE @Counter <= @NumColumns
BEGIN
SET @ColumnName = ( SELECT ColumnName
FROM @TableMetaData
WHERE ID = @Counter
)
SET @ParameterDataType = ISNULL(( SELECT DataType
+ ISNULL(CASE LOWER(Datatype)
WHEN 'varchar' THEN '(' + Length + ')'
WHEN 'char' THEN '(' + Length + ')'
WHEN 'varbinary' THEN '(' + Length + ')'
WHEN 'binary' THEN '(' + Length + ')'
WHEN 'nchar' THEN '(' + Length + ')'
WHEN 'nvarchar' THEN '(' + Length + ')'
WHEN 'decimal' THEN '(' + Length + ')'
END, '')
FROM @TableMetaData
WHERE ID = @Counter
), 'datatype error')
IF ( SELECT ispk
FROM @TableMetaData
WHERE id = @Counter
) = 1
BEGIN
SET @ParameterName = '@' + REPLACE(@ColumnName, ' ',
'') + ' '
+ @ParameterDataType + CHAR(10) + CHAR(13)
END
ELSE
BEGIN
SET @ParameterName = '--@' + REPLACE(@ColumnName, ' ',
'') + ' '
+ @ParameterDataType + CHAR(10) + CHAR(13)
END
SET @parameterlist = @parameterlist + @parametername
SET @Counter = @Counter + 1
END
SET @counter = 1
SET @WhereString = @WhereString + @PK + ' = @' + @PK
SET @SPName = ISNULL(@Prefix, '') + 'select' + REPLACE(@TableName, ' ', '')
+ 'ByID' + CHAR(10)
-- IF @CreateLog = 1
-- BEGIN
-- SET @usageUpdateStatement = @usageUpdateStatement
-- + SUBSTRING(@SPName, 0, LEN(@SpName)) + ''''
-- END
-- ELSE
-- BEGIN
-- SET @usageUpdateStatement = ''
-- END
SET @SPHeader = 'CREATE PROCEDURE ' + @SPName + CHAR(10) + CHAR(13)
WHILE @Counter <= @NumColumns
BEGIN
SET @ColumnName = ISNULL(( SELECT ColumnName
FROM @TableMetaData
WHERE ID = @Counter
), 'column error')
IF @Counter = @NumColumns
BEGIN
SET @SelectStatement = @SelectStatement + CHAR(10)
+ CHAR(13) + ' [' + @ColumnName + ']' + +CHAR(10)
+ CHAR(13) + 'FROM ' + CHAR(10) + CHAR(13) + '['
+ @TableName + ']'
END
ELSE
BEGIN
SET @SelectStatement = @SelectStatement + CHAR(10)
+ CHAR(13) + ' [' + @ColumnName + '],'
END
SET @Counter = @Counter + 1
END
SET @Statement = @SelectStatement + @WhereString
SET @SPText = ISNULL(@SPDescription, 'Description Error')
+ ISNULL(@SPHeader, 'Header Error') + ISNULL(@ParameterList, '')
+ CHAR(10) + CHAR(13) + 'AS' + CHAR(10) + CHAR(13) + 'BEGIN'
+ CHAR(10) + CHAR(13) + ISNULL(@Statement,
'Error may have occured because a primary key is not specified or a composite key is defined')
+ @UsageUpdateStatement + CHAR(10) + CHAR(13) + 'END'
INSERT INTO #sp ( sptext, spname )
SELECT @SPText AS sptext,
@SPName AS spname
--select all statement
-- SET @UsageUpdateStatement = CHAR(10) + CHAR(13)
-- + 'UPDATE [SPLOG] SET [USAGE] = [USAGE] + 1 WHERE SPNAME = '''
SET @SelectStatement = 'SELECT'
SET @ParameterList = ''
SET @ParameterName = ''
SET @Counter = 1
SET @SPName = ISNULL(@Prefix, '') + 'selectALL' + REPLACE(@TableName, ' ', '')
+ CHAR(10)
-- IF @CreateLog = 1
-- BEGIN
-- SET @usageUpdateStatement = @usageUpdateStatement
-- + SUBSTRING(@SPName, 0, LEN(@SpName)) + ''''
-- END
-- ELSE
-- BEGIN
-- SET @usageUpdateStatement = ''
-- END
SET @SPHeader = 'CREATE PROCEDURE ' + @SPName + CHAR(13)
WHILE @Counter <= @NumColumns
BEGIN
SET @ColumnName = ( SELECT ColumnName
FROM @TableMetaData
WHERE ID = @Counter
)
SET @ParameterDataType = ISNULL(( SELECT DataType
+ ISNULL(CASE LOWER(Datatype)
WHEN 'varchar' THEN '(' + Length + ')'
WHEN 'char' THEN '(' + Length + ')'
WHEN 'varbinary' THEN '(' + Length + ')'
WHEN 'binary' THEN '(' + Length + ')'
WHEN 'nchar' THEN '(' + Length + ')'
WHEN 'nvarchar' THEN '(' + Length + ')'
WHEN 'decimal' THEN '(' + Length + ')'
END, '')
FROM @TableMetaData
WHERE ID = @Counter
), 'datatype error')
IF @Counter = @NumColumns
BEGIN
SET @SelectStatement = @SelectStatement + CHAR(10)
+ CHAR(13) + ' [' + @ColumnName + ']' + +CHAR(10)
+ CHAR(13) + 'FROM ' + CHAR(10) + CHAR(13) + '['
+ @TableName + ']'
END
ELSE
BEGIN
SET @SelectStatement = @SelectStatement + CHAR(10)
+ CHAR(13) + ' [' + @ColumnName + '],'
END
SET @Counter = @Counter + 1
END
SET @Counter = 1
WHILE @Counter <= @NumColumns
BEGIN
SET @ColumnName = ( SELECT ColumnName
FROM @TableMetaData
WHERE ID = @Counter
)
SET @ParameterDataType = ISNULL(( SELECT DataType
+ ISNULL(CASE LOWER(Datatype)
WHEN 'varchar' THEN '(' + Length + ')'
WHEN 'char' THEN '(' + Length + ')'
WHEN 'varbinary' THEN '(' + Length + ')'
WHEN 'binary' THEN '(' + Length + ')'
WHEN 'nchar' THEN '(' + Length + ')'
WHEN 'nvarchar' THEN '(' + Length + ')'
WHEN 'decimal' THEN '(' + Length + ')'
END, '')
FROM @TableMetaData
WHERE ID = @Counter
), 'datatype error')
IF ( SELECT ispk
FROM @TableMetaData
WHERE id = @Counter
) = 1
BEGIN
SET @ParameterName = '--@' + REPLACE(@ColumnName, ' ',
'') + ' '
+ @ParameterDataType + CHAR(10) + CHAR(13)
END
ELSE
BEGIN
SET @ParameterName = '--@' + REPLACE(@ColumnName, ' ',
'') + ' '
+ @ParameterDataType + CHAR(10) + CHAR(13)
END
SET @parameterlist = @parameterlist + @parametername
SET @Counter = @Counter + 1
END
SET @Statement = @SelectStatement
SET @SPText = ISNULL(@SPDescription, 'Description Error')
+ ISNULL(@SPHeader, 'Header Error') + ISNULL(@ParameterList, '')
+ CHAR(10) + CHAR(13) + 'AS' + CHAR(10) + CHAR(13) + 'BEGIN'
+ CHAR(10) + CHAR(13) + ISNULL(@Statement,
'Error may have occured because a primary key is not specified or a composite key is defined')
+ @UsageUpdateStatement + CHAR(10) + CHAR(13) + 'END'
INSERT INTO #sp ( sptext, spname )
SELECT @SPText AS sptext,
@SPName AS spname
--INSERT STATEMENT
-- SET @UsageUpdateStatement = CHAR(10) + CHAR(13)
-- + 'UPDATE [SPLOG] SET [USAGE] = [USAGE] + 1 WHERE SPNAME = '''
SET @SPName = ISNULL(@Prefix, '') + 'insert' + REPLACE(@TableName, ' ', '')
+ CHAR(10)
-- IF @CreateLog = 1
-- BEGIN
-- SET @usageUpdateStatement = @usageUpdateStatement
-- + SUBSTRING(@SPName, 0, LEN(@SpName)) + ''''
-- END
-- ELSE
-- BEGIN
-- SET @usageUpdateStatement = ''
-- END
SET @SPHeader = 'CREATE PROCEDURE ' + @SPName + CHAR(13)
SET @Counter = 1
WHILE @Counter <= @NumColumns
BEGIN
SET @ColumnName = ( SELECT ColumnName
FROM @TableMetaData
WHERE ID = @Counter
)
SET @ParameterDataType = ( SELECT DataType
+ ISNULL(CASE LOWER(Datatype)
WHEN 'varchar' THEN '(' + Length + ')'
WHEN 'char' THEN '(' + Length + ')'
WHEN 'varbinary' THEN '(' + Length + ')'
WHEN 'binary' THEN '(' + Length + ')'
WHEN 'nchar' THEN '(' + Length + ')'
WHEN 'nvarchar' THEN '(' + Length + ')'
WHEN 'decimal' THEN '(' + Length + ')'
END, '')
FROM @TableMetaData
WHERE ID = @Counter
)
SET @ParameterName = '@' + REPLACE(@ColumnName, ' ', '')
IF @Counter = 1
BEGIN
IF ( SELECT ispk
FROM @TableMetaData
WHERE id = @Counter
) = 1
BEGIN
SET @InsertStatement = @InsertStatement + ' '
+ '[' + @TableName + ']' + CHAR(10)
+ CHAR(13) + ' (' + CHAR(10) + CHAR(13)
+ '--[' + @ColumnName + '],' + CHAR(10)
+ CHAR(13)
SET @InsertStatementValues = '--'
+ @ParameterName + ',' + CHAR(10)
+ CHAR(13)
SET @PrimaryKey = @ParameterName
SET @ParameterList = +@ParameterName + ' '
+ @ParameterDataType + ' OUTPUT, '
+ CHAR(10) + CHAR(13)
END
ELSE
BEGIN
SET @InsertStatement = @InsertStatement + ' '
+ @TableName + CHAR(10) + CHAR(13) + ' ('
+ CHAR(10) + CHAR(13) + '[' + @ColumnName
+ '],' + CHAR(10) + CHAR(13)
SET @InsertStatementValues = +@ParameterName
+ ',' + CHAR(10) + CHAR(13)
SET @ParameterList = '@' + @ParameterName
+ ' ' + @ParameterDataType + ', '
+ CHAR(10) + CHAR(13)
END
END
IF @Counter > 1
AND @Counter < @NumColumns
BEGIN
IF ( SELECT ispk
FROM @TableMetaData
WHERE id = @Counter
) = 1
BEGIN
SET @InsertStatement = @InsertStatement
+ ' --[' + @ColumnName + '],' + CHAR(10)
+ CHAR(13)
SET @InsertStatementValues = @InsertStatementValues
+ ' --' + @ParameterName + ',' + CHAR(10)
+ CHAR(13)
SET @ParameterList = '--' + @ParameterName
+ ' ' + @ParameterDataType + ', '
+ CHAR(10) + CHAR(13)
END
ELSE
BEGIN
SET @InsertStatement = @InsertStatement + ' ['
+ @ColumnName + '],' + CHAR(10) + CHAR(13)
SET @InsertStatementValues = @InsertStatementValues
+ ' ' + @ParameterName + ',' + CHAR(10)
+ CHAR(13)
SET @ParameterList = @ParameterList
+ @ParameterName + ' '
+ @ParameterDataType + ',' + CHAR(10)
+ CHAR(13)
END
END
IF @Counter = @NumColumns
BEGIN
IF ( SELECT ispk
FROM @TableMetaData
WHERE id = @Counter
) = 1
BEGIN
SET @InsertStatement = SUBSTRING(@InsertStatement, 0, LEN(@InsertStatement) - 1)
+ CHAR(10) + CHAR(13) + ')' + CHAR(10)
+ CHAR(13) + 'VALUES' + CHAR(10) + CHAR(13)
+ '('
SET @InsertStatementValues = @InsertStatementValues
+ ')'
SET @ParameterList = SUBSTRING(@ParameterList, 0, LEN(@ParameterList) - 3)
+ '--' + @ParameterName + ' '
+ @ParameterDataType
END
ELSE
BEGIN
SET @InsertStatement = @InsertStatement + '['
+ @ColumnName + '])' + CHAR(10) + CHAR(13)
+ 'VALUES' + CHAR(10) + CHAR(13) + '('
SET @InsertStatementValues = @InsertStatementValues
+ ' ' + @ParameterName + ')'
SET @ParameterList = @ParameterList
+ @ParameterName + ' '
+ @ParameterDataType
END
-- SET @InsertStatement = @InsertStatement + '['
-- + @ColumnName + '])' + CHAR(10) + CHAR(13)
-- + 'VALUES' + CHAR(10) + CHAR(13) + '('
-- SET @InsertStatementValues = @InsertStatementValues
-- + ' ' + @ParameterName + ')'
-- SET @ParameterList = @ParameterList + @ParameterName
-- + ' ' + @ParameterDataType
END
SET @Counter = @Counter + 1
END
SET @Statement = @InsertStatement + @InsertStatementValues
SET @SPText = ISNULL(@SPDescription, 'Description Error')
+ ISNULL(@SPHeader, 'Header Error') + ISNULL(@ParameterList, '')
+ CHAR(10) + CHAR(13) + 'AS' + CHAR(10) + CHAR(13) + 'BEGIN'
+ CHAR(10) + CHAR(13) + ISNULL(@Statement,
'Error may have occured because a primary key is not specified or a composite key is defined')
+ CHAR(10) + CHAR(13) + 'SET ' + @PrimaryKey
+ ' = SCOPE_IDENTITY() ' + CHAR(10) + CHAR(13)
+ @UsageUpdateStatement + CHAR(10) + CHAR(13) + 'END'
INSERT INTO #sp ( sptext, spname )
SELECT @SPText AS sptext,
@SPName AS spname
--update statement
-- SET @UsageUpdateStatement = CHAR(10) + CHAR(13)
-- + 'UPDATE [SPLOG] SET [USAGE] = [USAGE] + 1 WHERE SPNAME = '''
SET @WhereString = CHAR(10) + CHAR(13) + 'WHERE '
SET @ColumnParameter = ''
SET @SPName = ISNULL(@Prefix, '') + 'update' + REPLACE(@TableName, ' ', '')
+ CHAR(10)
-- IF @CreateLog = 1
-- BEGIN
-- SET @usageUpdateStatement = @usageUpdateStatement
-- + SUBSTRING(@SPName, 0, LEN(@SpName)) + ''''
-- END
-- ELSE
-- BEGIN
-- SET @usageUpdateStatement = ''
-- END
SET @SPHeader = 'CREATE PROCEDURE ' + @SPName + CHAR(13)
DECLARE @UpdateStatement VARCHAR(8000)
SET @UpdateStatement = 'UPDATE [' + @TableName + ']' + CHAR(10)
+ CHAR(13) + 'SET '
SET @Counter = 1
WHILE @Counter <= @NumColumns
BEGIN
SET @ColumnName = ( SELECT ColumnName
FROM @TableMetaData
WHERE ID = @Counter
)
SET @ParameterName = '@' + REPLACE(@ColumnName, ' ', '')
SET @ParameterDataType = ( SELECT DataType
+ ISNULL(CASE LOWER(Datatype)
WHEN 'varchar' THEN '(' + Length + ')'
WHEN 'char' THEN '(' + Length + ')'
WHEN 'varbinary' THEN '(' + Length + ')'
WHEN 'binary' THEN '(' + Length + ')'
WHEN 'nchar' THEN '(' + Length + ')'
WHEN 'nvarchar' THEN '(' + Length + ')'
WHEN 'decimal' THEN '(' + Length + ')'
END, '')
FROM @TableMetaData
WHERE ID = @Counter
)
IF @Counter = 1
BEGIN
SET @ParameterList = @ParameterName + ' '
+ @ParameterDataType + ', ' + CHAR(10) + CHAR(13)
END
IF @Counter = @NumColumns
BEGIN
SET @ColumnParameter = @ColumnParameter + '['
+ @ColumnName + ']' + ' = ' + @ParameterName
+ CHAR(10) + CHAR(13)
SET @ParameterList = @ParameterList + @ParameterName
+ ' ' + @ParameterDataType + CHAR(10) + CHAR(13)
END
IF @Counter > 1
AND @Counter < @NumColumns
BEGIN
SET @ColumnParameter = @ColumnParameter + '['
+ @ColumnName + ']' + ' = ' + @ParameterName + ','
+ CHAR(10) + CHAR(13)
SET @ParameterList = @ParameterList + @ParameterName
+ ' ' + @ParameterDataType + ',' + CHAR(10)
+ CHAR(13)
END
SET @Counter = @Counter + 1
END
SET @PK = ( SELECT ColumnName
FROM @TableMetaData
WHERE ispk = 1
)
SET @WhereString = @WhereString + @PK + ' = @' + @PK
SET @Statement = @UpdateStatement + @ColumnParameter + @WhereString
SET @SPText = ISNULL(@SPDescription, 'Description Error')
+ ISNULL(@SPHeader, 'Header Error') + ISNULL(@ParameterList, '')
+ CHAR(10) + CHAR(13) + 'AS' + CHAR(10) + CHAR(13) + 'BEGIN'
+ CHAR(10) + CHAR(13) + ISNULL(@Statement,
'Error may have occured because a primary key is not specified or a composite key is defined')
+ @UsageUpdateStatement + CHAR(10) + CHAR(13) + 'END'
INSERT INTO #sp ( sptext, spname )
SELECT @SPText AS sptext,
@SPName AS spname
END
--Script 2
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
--PART OF THE MACRO CODE EXECUTION BY CHRIS MORTON
--CONTACT CBMORTON@GMAIL.COM
--NO LIABLITITY CAN BE CLAIMED
--VERSION 1.1 BETA OCTOBER 23 2008
-- =============================================
create PROCEDURE [dbo].[_SPLOG_SPConstructor]
@AuthorName VARCHAR(50) = 'Chris Morton',
@CreateLog BIT = 1,
@Prefix VARCHAR(3) = NULL,
@TestMode BIT = 0
AS
BEGIN
CREATE TABLE #sp
(
spid BIGINT IDENTITY(1, 1),
sptext TEXT NOT NULL,
spname VARCHAR(100) NOT NULL
)
DECLARE @tables TABLE
(
id BIGINT IDENTITY(1, 1),
[table] VARCHAR(50)
)
INSERT INTO @tables ( [table] )
SELECT TABLE_NAME AS [table]
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME <> 'sysdiagrams'
AND table_name <> 'SPLOG'
DECLARE @tablename VARCHAR(50)
DECLARE @Counter BIGINT
SET @counter = 1
WHILE @counter <= ( SELECT MAX(id)
FROM @tables
)
BEGIN
SET @tablename = ( SELECT [TABLE]
FROM @tables
WHERE id = @counter
)
EXEC dbo._SPLOG_AutoGenStatements @tablename, @AuthorName, @Prefix, @CreateLog, @TestMode
SET @Counter = @counter + 1
END
DECLARE @sqlstring VARCHAR(8000)
DECLARE @spname VARCHAR(100)
SET @counter = 1
WHILE @counter <= ( SELECT MAX(spid)
FROM #sp
)
BEGIN
SET @sqlstring = ( SELECT sptext
FROM #sp
WHERE spid = @counter
)
SET @spname = ( SELECT spname
FROM #sp
WHERE spid = @counter
)
BEGIN TRY
IF @testmode = 0
BEGIN
EXEC ( @sqlstring
)
PRINT 'The stored procedure ' + @spname
+ ' was created successfully on '
+ CONVERT(VARCHAR, GETDATE())
END
ELSE
BEGIN
PRINT 'The stored procedure ' + @spname
+ ' was constructed successfully on '
+ CONVERT(VARCHAR, GETDATE())
END
END TRY
BEGIN CATCH
DECLARE @errormessage VARCHAR(200)
SET @errormessage = ( SELECT ERROR_MESSAGE()
)
PRINT 'The stored procedure ' + @spname
+ ' was not created.' + CHAR(10) + CHAR(13)
+ 'The Error was:' + CHAR(10) + CHAR(13)
+ @errormessage
END CATCH
SET @counter = @counter + 1
END
IF @CreateLog = 1
BEGIN
IF NOT EXISTS ( SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'SPLOG' )
BEGIN
CREATE TABLE SPLOG
(
spid BIGINT IDENTITY(1, 1),
sptext TEXT NOT NULL,
spname VARCHAR(200) NOT NULL,
createddate DATETIME DEFAULT GETDATE(),
usage BIGINT NOT NULL DEFAULT 0
)
INSERT INTO splog ( sptext, spname )
SELECT sptext,
RTRIM(spname)
FROM #sp
END
ELSE
BEGIN
INSERT INTO splog ( sptext, spname )
SELECT sptext,
spname
FROM #sp
END
END
DROP TABLE #sp
END 
