-- #########################################################
-- Author:	www.sqlbook.com
-- Copyright:	(c) www.sqlbook.com. You are free to use and redistribute
--		this script as long as this comments section with the 
--		author and copyright details are not altered.
-- Purpose:	For a specified user defined table (or all user defined
--		tables) in the database this script generates 4 Stored 
--		Procedure definitions with different Procedure name 
--		suffixes:
--		1) List all records in the table (suffix of  _lst)
--		2) Get a specific record from the table (suffix of _sel)
--		3) UPDATE or INSERT (UPSERT) - (suffix of _ups)
--		4) DELETE a specified row - (suffix of _del)
--		e.g. For a table called location the script will create
--		procedure definitions for the following procedures:
--		dbo.udp_Location_lst
--		dbo.udp_Location_sel
--		dbo.udp_Location_ups
--		dbo.udp_Location_del
-- Notes: 	The stored procedure definitions can either be printed
--		to the screen or executed using EXEC sp_ExecuteSQL.
--		The stored proc names are prefixed with udp_ to avoid 
--		conflicts with system stored procs.
-- Assumptions:	- This script assumes that the primary key is the first
--		column in the table and that if the primary key is
--		an integer then it is an IDENTITY (autonumber) field.
--		- This script is not suitable for the link tables
--		in the middle of a many to many relationship.
--		- After the script has run you will need to add
--		an ORDER BY clause into the '_lst' procedures
--		according to your needs / required sort order.
--		- Assumes you have set valid values for the 
--		config variables in the section immediately below
-- #########################################################

-- #########################################################
-- Enhancements by Essential Sofware:
-- 1) Suffixes changed to Search, Load, Save and Delete
-- 2) Generate a comment header for each procedure
-- 3) Generate default values when null parameters supplied to Save procedure
-- 4) Use char(13) + char(10) for new lines
-- #########################################################

-- ##########################################################
/* SET CONFIG VARIABLES THAT ARE USED IN SCRIPT */
-- ##########################################################

-- Do we want to generate the SP definitions for every user defined
-- table in the database or just a single specified table?
-- Assign a blank string - '' for all tables or the table name for
-- a single table.
DECLARE @GenerateProcsFor varchar(100)
--SET @GenerateProcsFor = 'YourTable'
SET @GenerateProcsFor = ''

-- which database do we want to create the procs for?
-- Change both the USE and SET lines below to set the datbase name
-- to the required database.
USE YourDatabaseName
DECLARE @DatabaseName varchar(100)
SET @DatabaseName = 'YourDatabaseName'

-- do we want the script to print out the CREATE PROC statements
-- or do we want to execute them to actually create the procs?
-- Assign a value of either 'Print' or 'Execute'
DECLARE @PrintOrExecute varchar(10)
SET @PrintOrExecute = 'Print'


-- Is there a table name prefix i.e. 'tbl_' which we don't want
-- to include in our stored proc names?
DECLARE @TablePrefix varchar(10)
SET @TablePrefix = 'tbl_'

-- For our '_lst' and '_sel' procedures do we want to 
-- do SELECT * or SELECT [ColumnName,]...
-- Assign a value of either 1 or 0
DECLARE @UseSelectWildCard bit
SET @UseSelectWildCard = 0

DECLARE @Author varchar(50)
SET @Author = 'Your Name';

-- ##########################################################
/* END SETTING OF CONFIG VARIABLE 
-- do not edit below this line */
-- ##########################################################


-- DECLARE CURSOR containing all columns from user defined tables
-- in the database
DECLARE TableCol Cursor FOR 
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, c.IS_NULLABLE, SUBSTRING(c.COLUMN_DEFAULT, 2, LEN(c.COLUMN_DEFAULT) - 2)
FROM INFORMATION_SCHEMA.Columns c INNER JOIN
	INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.Table_Catalog = @DatabaseName
	AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION

-- Declare variables which will hold values from cursor rows
DECLARE @TableSchema varchar(100), @TableName varchar(100)
DECLARE @ColumnName varchar(100), @DataType varchar(30)
DECLARE @CharLength int
DECLARE @IsNullable varchar(3)
DECLARE @ColumnDefault varchar(100)

DECLARE @ColumnNameCleaned varchar(100)

-- Declare variables which will track what table we are
-- creating Stored Procs for
DECLARE @CurrentTable varchar(100)
DECLARE @FirstTable bit
DECLARE @FirstColumnName varchar(100)
DECLARE @FirstColumnDataType varchar(30)
DECLARE @ObjectName varchar(100) -- this is the tablename with the 
				-- specified tableprefix lopped off.
DECLARE @TablePrefixLength int

-- init vars
SET @CurrentTable = ''
SET @FirstTable = 1
SET @TablePrefixLength = Len(@TablePrefix)

-- Declare variables which will hold the queries we are building use unicode
-- data types so that can execute using sp_ExecuteSQL
DECLARE @LIST nvarchar(4000), @UPSERT nvarchar(4000)
DECLARE @SELECT nvarchar(4000), @INSERT nvarchar(4000), @INSERTVALUES varchar(4000)
DECLARE @UPDATE nvarchar(4000), @DELETE nvarchar(4000)
DECLARE @DEFAULTS nvarchar(4000) = ''
DECLARE @CRLF varchar(2) = char(13) + char(10)

-- open the cursor
OPEN TableCol

-- get the first row of cursor into variables
FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength, @IsNullable, @ColumnDefault

-- loop through the rows of the cursor
WHILE @@FETCH_STATUS = 0 BEGIN

	SET @ColumnNameCleaned = Replace(@ColumnName, ' ', '')

	-- is this a new table?
	IF @TableName <> @CurrentTable BEGIN
		
		-- if is the end of the last table
		IF @CurrentTable <> '' BEGIN
			IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN

				-- first add any syntax to end the statement
				
				-- _lst
				SET @LIST = @List + @CRLF + 'FROM ' + @CurrentTable + @CRLF
				SET @LIST = @LIST + @CRLF + @CRLF + 'SET NOCOUNT OFF' + @CRLF + @CRLF
				SET @LIST = @LIST + @CRLF
				
				-- _sel
				SET @SELECT = @SELECT + @CRLF + 'FROM ' + @CurrentTable + @CRLF
				SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + @CRLF
				SET @SELECT = @SELECT + @CRLF + @CRLF + 'SET NOCOUNT OFF' + @CRLF + @CRLF
				SET @SELECT = @SELECT + @CRLF
	
	
				-- UPDATE (remove trailing comma and append the WHERE clause)
				SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE)- 2) + @CRLF + Char(9) + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + @CRLF
				
				-- INSERT
				SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 2) + @CRLF + Char(9) + ')' + @CRLF
				SET @INSERTVALUES = SUBSTRING(@INSERTVALUES, 0, LEN(@INSERTVALUES) -2) + @CRLF + Char(9) + ')'
				SET @INSERT = @INSERT + @INSERTVALUES
				
				-- _ups
				SET @UPSERT = @UPSERT + @CRLF + 'AS' + @CRLF
				SET @UPSERT = @UPSERT + 'SET NOCOUNT ON' + @CRLF + @CRLF
				SET @UPSERT = @UPSERT + @DEFAULTS + @CRLF + @CRLF
				IF @FirstColumnDataType IN ('int', 'bigint', 'smallint', 'tinyint', 'float', 'decimal')
				BEGIN
					SET @UPSERT = @UPSERT + 'IF ISNULL(@' + Replace(@FirstColumnName, ' ', '') + ',0) = 0 BEGIN' + @CRLF
				END ELSE BEGIN
					SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = '''' BEGIN' + @CRLF	
				END
				SET @UPSERT = @UPSERT + ISNULL(@INSERT, '') + @CRLF
				SET @UPSERT = @UPSERT + Char(9) + 'RETURN SCOPE_IDENTITY()' + @CRLF
				SET @UPSERT = @UPSERT + 'END' + @CRLF
				SET @UPSERT = @UPSERT + 'ELSE BEGIN' + @CRLF
				SET @UPSERT = @UPSERT + ISNULL(@UPDATE, '') + @CRLF
				SET @UPSERT = @UPSERT + 'END' + @CRLF + @CRLF
				SET @UPSERT = @UPSERT + 'SET NOCOUNT OFF' + @CRLF + @CRLF
				SET @UPSERT = @UPSERT + @CRLF
	
				-- _del
				-- delete proc completed already
	
				-- --------------------------------------------------
				-- now either print the SP definitions or 
				-- execute the statements to create the procs
				-- --------------------------------------------------
				IF @PrintOrExecute <> 'Execute' BEGIN
					PRINT @LIST
					PRINT @SELECT
					PRINT @UPSERT
					PRINT @DELETE
				END ELSE BEGIN
					EXEC sp_Executesql @LIST
					EXEC sp_Executesql @SELECT
					EXEC sp_Executesql @UPSERT
					EXEC sp_Executesql @DELETE
				END
			END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
		END
		
		-- update the value held in @CurrentTable
		SET @CurrentTable = @TableName
		SET @FirstColumnName = @ColumnName
		SET @FirstColumnDataType = @DataType
		
		IF @TablePrefixLength > 0 BEGIN
			IF SUBSTRING(@CurrentTable, 1, @TablePrefixLength) = @TablePrefix BEGIN
				--PRINT @CRLF + 'DEBUG: OBJ NAME: ' + RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)
				SET @ObjectName = RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)
			END ELSE BEGIN
				SET @ObjectName = @CurrentTable
			END
		END ELSE BEGIN
			SET @ObjectName = @CurrentTable
		END
		
		IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
		
			-- ----------------------------------------------------
			-- now start building the procedures for the next table
			-- ----------------------------------------------------
			
			-- _lst
			SET @LIST = 'CREATE PROC [dbo].[' + @ObjectName + 'Search]' + @CRLF
			SET @LIST = @LIST + 'AS' + @CRLF + @CRLF

			SET @LIST = @LIST + '-- =================================================================================' + @CRLF
			SET @LIST = @LIST + '-- Description: Returns ' + @ObjectName + ' records for the parameters passed in.' + @CRLF
			SET @LIST = @LIST + '-- History:     ' + UPPER(REPLACE(CONVERT(VARCHAR(15), GETDATE(), 106),' ','-')) + '  ' + @Author + '  Created' + @CRLF
			SET @LIST = @LIST + '-- =================================================================================' + @CRLF + @CRLF

			SET @LIST = @LIST + 'SET NOCOUNT ON' + @CRLF
			IF @UseSelectWildcard = 1 BEGIN
				SET @LIST = @LIST + @CRLF + 'SELECT * '
			END 
			ELSE BEGIN
				SET @LIST = @LIST + @CRLF + 'SELECT [' + @ColumnName + ']'
			END
	
			-- _sel
			SET @SELECT = 'CREATE PROC [dbo].[' + @ObjectName + 'Load]' + @CRLF + @CRLF

			SET @SELECT = @SELECT + '-- =================================================================================' + @CRLF
			SET @SELECT = @SELECT + '-- Description: Returns single ' + @ObjectName + ' record for the parameters passed in.' + @CRLF
			SET @SELECT = @SELECT + '-- History:     ' + UPPER(REPLACE(CONVERT(VARCHAR(15), GETDATE(), 106),' ','-')) + '  ' + @Author + '  Created' + @CRLF
			SET @SELECT = @SELECT + '-- =================================================================================' + @CRLF + @CRLF

			SET @SELECT = @SELECT + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
			IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
				SET @SELECT = @SELECT + '(' + CAST(@CharLength As varchar(10)) + ')'
			END
			SET @SELECT = @SELECT + @CRLF + 'AS' + @CRLF
			SET @SELECT = @SELECT + 'SET NOCOUNT ON' + @CRLF
			IF @UseSelectWildcard = 1 BEGIN
				SET @SELECT = @SELECT + @CRLF + 'SELECT * '
			END 
			ELSE BEGIN
				SET @SELECT = @SELECT + @CRLF + 'SELECT [' + @ColumnName + ']'
			END
	
			-- _ups
			SET @UPSERT = 'CREATE PROC [dbo].[' + @ObjectName + 'Save]' + @CRLF + @CRLF

			SET @UPSERT = @UPSERT + '-- ========================================================================================' + @CRLF
			SET @UPSERT = @UPSERT + '-- Description: Inserts or updates ' + @ObjectName + ' record for the parameters passed in.' + @CRLF
			SET @UPSERT = @UPSERT + '-- History:     ' + UPPER(REPLACE(CONVERT(VARCHAR(15), GETDATE(), 106),' ','-')) + '  ' + @Author + '  Created' + @CRLF
			SET @UPSERT = @UPSERT + '-- ========================================================================================' + @CRLF + @CRLF

    		SET @UPSERT = @UPSERT + @CRLF + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
			IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
				SET @UPSERT = @UPSERT + '(' + CAST(@CharLength As Varchar(10)) + ')'
			END
	
	        IF @IsNullable = 'NO' AND @ColumnDefault IS NOT NULL BEGIN
				SET @DEFAULTS = 'IF @' + @ColumnNameCleaned + ' IS NULL SET @'  + @ColumnNameCleaned + ' = ' + @ColumnDefault + @CRLF
			END

			-- UPDATE
			SET @UPDATE = Char(9) + 'UPDATE ' + @TableName + ' SET ' + @CRLF
			
			-- INSERT -- don't add first column to insert if it is an
			--	     integer (assume autonumber)
			SET @INSERT = Char(9) + 'INSERT INTO ' + @TableName + ' (' + @CRLF
			SET @INSERTVALUES = Char(9) + 'VALUES (' + @CRLF
			
			IF @FirstColumnDataType NOT IN ('int', 'bigint', 'smallint', 'tinyint')
			BEGIN
				SET @INSERT = @INSERT + Char(9) + Char(9) + '[' + @ColumnName + '],' + @CRLF
				SET @INSERTVALUES = @INSERTVALUES + Char(9) + Char(9) + '@' + @ColumnNameCleaned + ',' + @CRLF
			END
	
			-- _del
			SET @DELETE = 'CREATE PROC [dbo].[' + @ObjectName + 'Delete]' + @CRLF + @CRLF

			SET @DELETE = @DELETE + '-- =================================================================================' + @CRLF
			SET @DELETE = @DELETE + '-- Description: Deletes ' + @ObjectName + ' record for parameter passed in.' + @CRLF
			SET @DELETE = @DELETE + '-- History:     ' + UPPER(REPLACE(CONVERT(VARCHAR(15), GETDATE(), 106),' ','-')) + '  ' + @Author + '  Created' + @CRLF
			SET @DELETE = @DELETE + '-- =================================================================================' + @CRLF + @CRLF
			SET @DELETE = @DELETE + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
			IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
				SET @DELETE = @DELETE + '(' + CAST(@CharLength As Varchar(10)) + ')'
			END
			SET @DELETE = @DELETE + @CRLF + 'AS' + @CRLF
			SET @DELETE = @DELETE + 'SET NOCOUNT ON' + @CRLF + @CRLF
			SET @DELETE = @DELETE + 'DELETE FROM ' + @TableName + @CRLF
			SET @DELETE = @DELETE + 'WHERE [' + @ColumnName + '] = @' + @ColumnNameCleaned + @CRLF
			SET @DELETE = @DELETE + @CRLF + 'SET NOCOUNT OFF' + @CRLF
			SET @DELETE = @DELETE + @CRLF 

		END	-- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
	END
	ELSE BEGIN
		IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
		
			-- is the same table as the last row of the cursor
			-- just append the column
			
			-- _lst
			IF @UseSelectWildCard = 0 BEGIN
				SET @LIST = @LIST + ', ' + @CRLF + Char(9) + '[' + @ColumnName + ']'
			END
	
			-- _sel
			IF @UseSelectWildCard = 0 BEGIN
				SET @SELECT = @SELECT + ', ' + @CRLF + Char(9) + '[' + @ColumnName + ']'
			END
	
			-- _ups
			SET @UPSERT = @UPSERT + ',' + @CRLF + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
			IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN
				SET @UPSERT = @UPSERT + '(' + CAST(@CharLength As varchar(10)) + ')'
			END

	        IF @IsNullable = 'NO' AND @ColumnDefault IS NOT NULL BEGIN
				SET @DEFAULTS = @DEFAULTS + 'IF @' + @ColumnNameCleaned + ' IS NULL SET @'  + @ColumnNameCleaned + ' = ' + @ColumnDefault + @CRLF
			END
	
			-- UPDATE
			SET @UPDATE = @UPDATE + Char(9) + Char(9) + '[' + @ColumnName + '] = @' + @ColumnNameCleaned + ',' + @CRLF
	
			-- INSERT
			SET @INSERT = @INSERT + Char(9) + Char(9) + '[' + @ColumnName + '],' + @CRLF
			SET @INSERTVALUES = @INSERTVALUES + Char(9) + Char(9) + '@' + @ColumnNameCleaned + ',' + @CRLF
	
			-- _del
			-- delete proc completed already
		END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable'
	END

	-- fetch next row of cursor into variables
	FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength, @IsNullable, @ColumnDefault
END

-- ----------------
-- clean up cursor
-- ----------------
CLOSE TableCol
DEALLOCATE TableCol

-- ------------------------------------------------
-- repeat the block of code from within the cursor
-- So that the last table has its procs completed
-- and printed / executed
-- ------------------------------------------------

-- if is the end of the last table
IF @CurrentTable <> '' BEGIN
	IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN

		-- first add any syntax to end the statement
		
		-- _lst
		SET @LIST = @List + @CRLF + 'FROM ' + @CurrentTable + @CRLF
		SET @LIST = @LIST + @CRLF + @CRLF + 'SET NOCOUNT OFF' + @CRLF
		SET @LIST = @LIST + @CRLF
		
		-- _sel
		SET @SELECT = @SELECT + @CRLF + 'FROM ' + @CurrentTable + @CRLF
		SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + @CRLF
		SET @SELECT = @SELECT + @CRLF + @CRLF + 'SET NOCOUNT OFF' + @CRLF
		SET @SELECT = @SELECT + @CRLF


		-- UPDATE (remove trailing comma and append the WHERE clause)
		SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE)- 2) + @CRLF + Char(9) + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + @CRLF
		
		-- INSERT
		SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 2) + @CRLF + Char(9) + ')' + @CRLF
		SET @INSERTVALUES = SUBSTRING(@INSERTVALUES, 0, LEN(@INSERTVALUES) - 2) + @CRLF + Char(9) + ')'
		SET @INSERT = @INSERT + @INSERTVALUES
		
		-- _ups
		SET @UPSERT = @UPSERT + @CRLF + 'AS' + @CRLF
		SET @UPSERT = @UPSERT + 'SET NOCOUNT ON' + @CRLF
		IF @FirstColumnDataType IN ('int', 'bigint', 'smallint', 'tinyint', 'float', 'decimal')
		BEGIN
			SET @UPSERT = @UPSERT + 'IF ISNULL(@' + Replace(@FirstColumnName, ' ', '') + ',0) = 0 BEGIN' + @CRLF
		END ELSE BEGIN
			SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = '''' BEGIN' + @CRLF	
		END
		SET @UPSERT = @UPSERT + ISNULL(@INSERT, '') + @CRLF
		SET @UPSERT = @UPSERT + Char(9) + 'SELECT SCOPE_IDENTITY() As InsertedID' + @CRLF
		SET @UPSERT = @UPSERT + 'END' + @CRLF
		SET @UPSERT = @UPSERT + 'ELSE BEGIN' + @CRLF
		SET @UPSERT = @UPSERT + ISNULL(@UPDATE, '') + @CRLF
		SET @UPSERT = @UPSERT + 'END' + @CRLF + @CRLF
		SET @UPSERT = @UPSERT + 'SET NOCOUNT OFF' + @CRLF
		SET @UPSERT = @UPSERT + @CRLF

		-- _del
		-- delete proc completed already

		-- --------------------------------------------------
		-- now either print the SP definitions or 
		-- execute the statements to create the procs
		-- --------------------------------------------------
		IF @PrintOrExecute <> 'Execute' BEGIN
			PRINT @LIST
			PRINT @SELECT
			PRINT @UPSERT
			PRINT @DELETE
		END ELSE BEGIN
			EXEC sp_Executesql @LIST
			EXEC sp_Executesql @SELECT
			EXEC sp_Executesql @UPSERT
			EXEC sp_Executesql @DELETE
		END
	END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable
END