--USER VARIABLES
DECLARE @SchemaName varchar(25),
		@TableName varchar(255),
		@JiraNumber varchar(15),
		@Company varchar(50), 
		@CreatorAbbv char(10),
		@PrintOrExecute varchar(10),
		@IncludeExistance bit 
		
SET @SchemaName = 'CONFIG'
SET @TableName = 'GeneralSettings'
SET @CreatorAbbv = 'Joel Ewald'
SET @JiraNumber = 'SIN-9'
SET @Company = 'Improvement Path Systems, Inc.'
SET @PrintOrExecute = 'Print'
SET @IncludeExistance = 0

--STATIC VARIABLES WE WILL USE
DECLARE @Header varchar(8000)

IF COALESCE(@SchemaName,'') = ''
	SET @SchemaName = 'dbo'
	
IF NOT EXISTS ( SELECT 1
				FROM INFORMATION_SCHEMA.TABLES 
				WHERE TABLE_SCHEMA = @SchemaName
				  AND TABLE_NAME = @TableName
				  AND TABLE_TYPE = 'BASE TABLE' ) 
BEGIN
	RAISERROR('Table could not be found in the database.',16,1)
	GOTO AllDone
END

--CREATE THE BASIC HEADER
SET @Header = 
'--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--+  Copyright + ' + CAST(YEAR(GETDATE()) as Varchar(4)) + ', ' + @Company + '
--+  All rights reserved.
--+
--+  Description:
--+		*** [DESCRIPTION]
--+
--+  Date		Developer	Description
--+  ----		--------- 	-----------
--+  ' + CONVERT(varchar(10),GETDATE(),101) + ' ' + @CreatorAbbv + '  ' + @JiraNumber + ' Initial Implementation
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'

DECLARE @Base varchar(8000)
SET @Base = ''
IF @IncludeExistance = 1
BEGIN
	SET @Base = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''' + @SchemaName + '.usp_' + @TableName + '[PROCTYPE]'') AND type in (N''P'', N''PC''))
	DROP PROCEDURE ' + @SchemaName + '.usp_' + @TableName + '[PROCTYPE]
	GO
'
END

SET @Base = @Base + '
CREATE PROCEDURE ' + @SchemaName + '.usp_' + @TableName + '[PROCTYPE]'

DECLARE @Try varchar(1000)
SET @Try = '
AS
SET NOCOUNT ON
BEGIN TRY
'

DECLARE @Catch varchar(8000)
SET @Catch = '

END TRY
BEGIN CATCH
	EXECUTE APP.[usp_SystemErrors_Database_Insert] 1
END CATCH
GO


'


-- DECLARE CURSOR containing all columns from user defined tables
-- in the database
DECLARE TableCol Cursor FOR 
SELECT C.COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH, C.COLUMN_DEFAULT, C.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS C 
JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME
WHERE T.TABLE_SCHEMA = @SchemaName
  AND T.TABLE_NAME = @TableName
  AND T.TABLE_TYPE = 'BASE TABLE'
  AND C.COLUMN_NAME <> 'SystemRecord'
ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION

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

-- Declare variables which will track what table we are
-- creating Stored Procs for
DECLARE @FirstColumnName varchar(100),
		@FirstColumnDataType varchar(30)

-- Declare variables which will hold the queries we are building use unicode
-- data types so that can execute using sp_ExecuteSQL
DECLARE @LIST varchar(max),
		@SELECT varchar(max), 
		@INSERT varchar(max),
		@UPDATE varchar(max), 
		@DELETE varchar(max)
		
DECLARE @ColumnList1 varchar(max),
		@ColumnList2 varchar(max),
		@ColumnList3 varchar(max),
		@ColumnList4 varchar(max),
		@ColumnList5 varchar(max),
		@ColumnList6 varchar(max)


--CREATE THE STANDARD INSERT 
SET @INSERT = REPLACE(@Base,'[PROCTYPE]','_Insert') + '
' + REPLACE(@Header,'[DESCRIPTION]',  'Procedure creates a single ' + @SchemaName + '.' + @Tablename + ' record.') 

SET @UPDATE = REPLACE(@Base,'[PROCTYPE]','_Update') + '
' + REPLACE(@Header,'[DESCRIPTION]',  'Procedure updates a single ' + @SchemaName + '.' + @Tablename + ' record.') 

SET @DELETE = REPLACE(@Base,'[PROCTYPE]','_Delete') + '
' + REPLACE(@Header,'[DESCRIPTION]',  'Procedure deletes a single ' + @SchemaName + '.' + @Tablename + ' record.') 

SET @LIST = REPLACE(@Base,'[PROCTYPE]','_GetListing') + '
' + REPLACE(@Header,'[DESCRIPTION]',  'Procedure returns a listing for ' + @SchemaName + '.' + @Tablename + '.') 

SET @SELECT = REPLACE(@Base,'[PROCTYPE]','_GetInfoById') + '
' + REPLACE(@Header,'[DESCRIPTION]',  'Procedure returns a ' + @SchemaName + '.' + @Tablename + ' record by ID.') 



DECLARE @Counter int; SET @Counter = 1

-- open the cursor
OPEN TableCol

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

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

	SET @ColumnNameCleaned = REPLACE(@ColumnName, ' ', '')
		
	IF @Counter = 1
	BEGIN
		SET @FirstColumnName = @ColumnNameCleaned
		SET @FirstColumnDataType = @DataType
	
		--for the select / delete set the id to select on	
		SET @SELECT = @SELECT + Char(13) + '@' + @ColumnNameCleaned + ' ' + @DataType
		SET @DELETE = @DELETE + Char(13) + '@' + @ColumnNameCleaned + ' ' + @DataType
		
		IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') 
		BEGIN
			SET @SELECT = @SELECT + '(' + CAST(@CharLength As varchar(10)) + ')' 
			SET @DELETE = @DELETE + '(' + CAST(@CharLength As varchar(10)) + ')' 
		END
		
	END

	IF @Counter = 2
	BEGIN
		SET @ColumnList5 = '@' + @ColumnNameCleaned + ' ' + @DataType
	END
		
	IF @Counter = 1  
	BEGIN
		SET @ColumnList1 = '@' + @ColumnNameCleaned + ' ' + @DataType
		SET @ColumnList2 = '	(	' + @ColumnNameCleaned
		SET @ColumnList3 = '	SET ' 
		SET @ColumnList4 = '		' + @ColumnNameCleaned
		SET @ColumnList6 = '	(	@' + @ColumnNameCleaned
	END
	ELSE
	BEGIN
		SET @ColumnList1 = @ColumnList1 + ',
@' + @ColumnNameCleaned + ' ' + @DataType
		SET @ColumnList2 = @ColumnList2 + CASE WHEN @Counter % 6 = 0 THEN ', 
		' ELSE ', ' END + @ColumnNameCleaned
		SET @ColumnList4 = @ColumnList4 + ',
		' + @ColumnNameCleaned
		SET @ColumnList6 = @ColumnList6 + ',
		@' + @ColumnNameCleaned

		IF @Counter = 2
		BEGIN
			SET @ColumnList3 = @ColumnList3 + @ColumnNameCleaned + ' = @' +  @ColumnNameCleaned
		END
		
		IF @Counter > 2
		BEGIN		
			SET @ColumnList5 = @ColumnList5 + ',
@' + @ColumnNameCleaned + ' ' + @DataType

			SET @ColumnList3 = @ColumnList3 + ',' + CHAR(13) + '		'+ @ColumnNameCleaned + ' = @' +  @ColumnNameCleaned
		END
	END
				
	IF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') 
	BEGIN
		SET @ColumnList1 = @ColumnList1 + '(' + CAST(@CharLength As varchar(10)) + ')'
		SET @ColumnList5 = @ColumnList5 + '(' + CAST(@CharLength As varchar(10)) + ')'
	END

	IF COALESCE(@ColumnDefault,'') > '' OR @IsNullable = 'YES'
	BEGIN
		IF COALESCE(@ColumnDefault,'') > ''
		BEGIN
			SET @ColumnList1 = @ColumnList1 + ' = ' + REPLACE(REPLACE(@ColumnDefault,'(',''),')','')
			SET @ColumnList5 = @ColumnList5 + ' = ' + REPLACE(REPLACE(@ColumnDefault,'(',''),')','')
		END
		ELSE
		BEGIN
			SET @ColumnList1 = @ColumnList1 + ' = NULL'
			SET @ColumnList5 = @ColumnList5 + ' = NULL'
		END
	END
	
		
	IF @ColumnName IN ( 'ChangedDate', 'UpdateDatetime' )
	BEGIN
		SET @ColumnList1 = @ColumnList1 + ' OUTPUT'
		SET @ColumnList5 = @ColumnList5 + ' OUTPUT'
	END
	
	SET @Counter = @Counter + 1

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

CLOSE TableCol
DEALLOCATE TableCol

SET @ColumnList5 = @ColumnList5 + ',
@' + @FirstColumnName + ' int OUTPUT'
		
--FINISH OFF THE PROCS
SET @LIST = @LIST + @Try + '
	SELECT
' + @ColumnList4 + '
	FROM ' + @SchemaName + '.' + @Tablename + @Catch 

SET @SELECT = @SELECT + '
' + @Try + '
	SELECT
' + @ColumnList4 + '
	FROM ' + @SchemaName + '.' + @Tablename + '
	WHERE ' + @FirstColumnName + ' = @' + @FirstColumnName + @Catch 

SET @INSERT = @INSERT + '
' + @ColumnList5 + '
' + @Try + '
	INSERT INTO ' + @SchemaName + '.' + @TableName + '
' + @ColumnList2 + ' )
	VALUES
' + @ColumnList6 + '	)

	SET @' + @FirstColumnName + ' = SCOPE_IDENTITY()' + @Catch

SET @DELETE = @DELETE + '
' + @Try + '
	DELETE FROM ' + @SchemaName + '.' + @TableName + '
	WHERE ' + @FirstColumnName + ' = @' + @FirstColumnName + @Catch
 
SET @UPDATE = @UPDATE + '
' + @ColumnList1 + '
' + @Try + '
	UPDATE ' + @SchemaName + '.' + @TableName + '
' + @ColumnList3 + '
	WHERE ' + @FirstColumnName + ' = @' + @FirstColumnName + @Catch


IF @PrintOrExecute <> 'Execute' 
BEGIN
	PRINT @INSERT
	PRINT @UPDATE
	PRINT @DELETE
	PRINT @LIST
	PRINT @SELECT
END 
ELSE 
BEGIN
	EXEC sp_Executesql @INSERT
	EXEC sp_Executesql @UPDATE
	EXEC sp_Executesql @DELETE
	EXEC sp_Executesql @LIST
	EXEC sp_Executesql @SELECT
END
	
	
AllDone:
	
GO