Stored procedure to dynamically create insert, update, delete stored procedures

Question

I have a stored procedure shown below, it creates insert, update, delete stored procedures automatically for all existed tables. But it is not work for all tables in AdventureWorks2012 database. Any idea?

    USE [AdventureWorks2012]
GO
/****** Object:  StoredProcedure [dbo].[sp_et_create_sps_for_table]    Script Date: 11/16/2012 10:28:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_et_create_sps_for_table]
AS
BEGIN
DECLARE Tables_cursor CURSOR
FOR
SELECT TABLE_SCHEMA,TABLE_NAME
From INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
OPEN Tables_cursor
DECLARE @Table_Schema nvarchar(50),@Table_Name nvarchar(50)
Fetch next
from Tables_cursor
INTO @Table_Schema,@Table_Name
WHILE @@FETCH_STATUS=0
BEGIN
    DECLARE @tblName Varchar(50)
    set @tblName=@Table_Schema+'.'+@Table_Name;
    print @tblName
    --set @tblName='Sales.SpecialOffer'
Declare @dbName Varchar(50)
Declare @insertSPName Varchar(50), @updateSPName Varchar(50), @deleteSPName Varchar(50) ;
Declare @tablColumnParameters Varchar(1000), @tableColumns Varchar(1000),@tableColumnVariables Varchar(1000);
Declare @tableCols Varchar(1000), @tablinsertParameters Varchar(1000);
Declare @space Varchar(50) ;
Declare @colName Varchar(100) ;
Declare @colVariable Varchar(100) ;
Declare @colParameter Varchar(100) ;
Declare @strSpText Varchar(4000);
Declare @updCols Varchar(2000);
Declare @delParamCols Varchar(2000);
Declare @whereCols Varchar(2000);
Set @tblName = SubString(@tblName,CharIndex('.',@tblName)+1, Len(@tblName))
Set @insertSPName = '[dbo].[sp_' + lower(@tblName) +'_insert]' ;
Set @updateSPName = '[dbo].[sp_' + lower(@tblName) +'_update]' ;
Set @deleteSPName = '[dbo].[sp_' + lower(@tblName) +'_delete]' ;
Set @space = REPLICATE(' ', 4) ;
--print @space+'dd';
Set @tablColumnParameters = '' ;
Set @tableColumns = '' ;
Set @tableColumnVariables = '' ;
Set @strSPText = '' ;
Set @tableCols = '' ;
Set @updCols = '' ;
Set @delParamCols = '' ;
Set @whereCols = '' ;
SET NOCOUNT ON

-- Get all Primary KEY columns & data types for a table
SELECT t.name as 'Table',
c.colid ,
'[' + c.name + ']' as 'ColumnName',
'@'+c.name as 'ColumnVariable',
systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),c.length) +')' Else '' end as 'DataType' ,
'@'+c.name + ' ' + systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),c.length) +')' Else '' end as 'ColumnParameter'
Into #tmp_PK_Structure
FROM sysindexes i, sysobjects t, sysindexkeys k, syscolumns c, systypes
WHERE i.id = t.id AND
i.indid = k.indid AND i.id = k.ID And
c.id = t.id AND c.colid = k.colid AND
i.indid BETWEEN 1 And 254 AND
c.xusertype = systypes.xusertype AND
(i.status & 2048) = 2048 AND t.id = OBJECT_ID(@tblName)


SELECT distinct
sysobjects.name as 'Table',
syscolumns.colid ,
'[' + syscolumns.name + ']' as 'ColumnName',
'@'+syscolumns.name as 'ColumnVariable',
systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'DataType' ,
'@'+syscolumns.name + ' ' + systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'ColumnParameter'
Into #tmp_Structure
From sysobjects , syscolumns , systypes
Where sysobjects.id = syscolumns.id
and syscolumns.xusertype = systypes.xusertype
and sysobjects.xtype = 'u'
and sysobjects.name = @tblName
and syscolumns.name!=(SELECT
c.name AS ColumnName
FROM sys.columns AS c
INNER JOIN
sys.tables AS t
ON t.[object_id] = c.[object_id]
where c.is_identity = 1
and t.name=@tblName)
Order by syscolumns.colid


SELECT distinct
sysobjects.name as 'Table',
syscolumns.colid ,
'[' + syscolumns.name + ']' as 'ColumnName',
'@'+syscolumns.name as 'ColumnVariable',
systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'DataType' ,
'@'+syscolumns.name + ' ' + systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then '(' + Convert(varchar(10),Case When syscolumns.length=-1 Then 4000 else syscolumns.length end) +')' Else '' end as 'ColumnParameter'
Into #tmp_Structure1
From sysobjects , syscolumns , systypes
Where sysobjects.id = syscolumns.id
and syscolumns.xusertype = systypes.xusertype
and sysobjects.xtype = 'u'
and sysobjects.name = @tblName
Order by syscolumns.colid
--END

/* Read the table structure and populate variables*/
Declare SpText_Cursor Cursor For
Select ColumnName, ColumnVariable, ColumnParameter
From #tmp_Structure

Open SpText_Cursor

Fetch Next From SpText_Cursor Into @colName, @colVariable, @colParameter
While @@FETCH_STATUS = 0
Begin
Set @tableColumns = @tableColumns + @colName + CHAR(13) + @space + @space + ',' ;
Set @tablColumnParameters = @tablColumnParameters + @colParameter + CHAR(13) + @space + ',' ;
Set @tableColumnVariables = @tableColumnVariables + @colVariable + CHAR(13) + @space + @space + ',' ;
Set @tableCols = @tableCols + @colName + ',' ;
Set @updCols = @updCols + @colName + ' = ' + @colVariable + CHAR(13) + @space + @space + ',' ;
Fetch Next From SpText_Cursor Into @colName, @colVariable, @colParameter
End

Close SpText_Cursor
Deallocate SpText_Cursor

/* for update parameter*/

if exists(select * from #tmp_PK_Structure)
BEGIN
Set @tablinsertParameters=''
Declare SpText_Cursor1 Cursor For
Select ColumnParameter
From #tmp_Structure1

Open SpText_Cursor1

Fetch Next From SpText_Cursor1 Into @colParameter
While @@FETCH_STATUS = 0
Begin

Set @tablinsertParameters = @tablinsertParameters + @colParameter + CHAR(13) + @space + ',' ;
Fetch Next From SpText_Cursor1 Into @colParameter
End

Close SpText_Cursor1
Deallocate SpText_Cursor1
END
/*end for update parameter*/

/* Read the Primary Keys from the table and populate variables*/
Declare SpPKText_Cursor Cursor For
Select ColumnName, ColumnVariable, ColumnParameter
From #tmp_PK_Structure

Open SpPKText_Cursor

Fetch Next From SpPKText_Cursor Into @colName, @colVariable, @colParameter
While @@FETCH_STATUS = 0
Begin
Set @delParamCols = @delParamCols + @colParameter + CHAR(13) + @space + ',' ;
Set @whereCols = @whereCols + @colName + ' = ' + @colVariable + ' AND ' ;
Fetch Next From SpPKText_Cursor Into @colName, @colVariable, @colParameter
End

Close SpPKText_Cursor
Deallocate SpPKText_Cursor


If (LEN(@tablinsertParameters)>0)
Set @tablinsertParameters = LEFT(@tablinsertParameters,LEN(@tablinsertParameters)-1) ;

-- Stored procedure scripts starts here
If (LEN(@tablColumnParameters)>0)
Begin
Set @tablColumnParameters = LEFT(@tablColumnParameters,LEN(@tablColumnParameters)-1) ;
Set @tableColumnVariables = LEFT(@tableColumnVariables,LEN(@tableColumnVariables)-1) ;
Set @tableColumns = LEFT(@tableColumns,LEN(@tableColumns)-1) ;
Set @tableCols = LEFT(@tableCols,LEN(@tableCols)-1) ;
Set @updCols = LEFT(@updCols,LEN(@updCols)-1) ;

If (LEN(@whereCols)>0)
Begin
Set @whereCols = 'WHERE ' + LEFT(@whereCols,LEN(@whereCols)-4) ;
Set @delParamCols = LEFT(@delParamCols,LEN(@delParamCols)-1) ;
End

/* Create INSERT stored procedure for the table if it does not exist */
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@insertSPName) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
Begin
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + '/*-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + '-- Author : dbo'
Set @strSPText = @strSPText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + '-- Description : Insert Procedure for ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @insertSPName + ' ' + @tableCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @insertSPName

if exists(select * from #tmp_PK_Structure)
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParameters
else
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablinsertParameters


Set @strSPText = @strSPText + CHAR(13) + 'AS'
Set @strSPText = @strSPText + CHAR(13) + 'BEGIN'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + @space + 'INSERT INTO [dbo].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space + '( '
Set @strSPText = @strSPText + CHAR(13) + @space + @space + ' ' + @tableColumns
Set @strSPText = @strSPText + CHAR(13) + @space + ')'
Set @strSPText = @strSPText + CHAR(13) + @space + 'VALUES'
Set @strSPText = @strSPText + CHAR(13) + @space + '('
Set @strSPText = @strSPText + CHAR(13) + @space + @space + ' ' + @tableColumnVariables
Set @strSPText = @strSPText + CHAR(13) + @space + ')'
Set @strSPText = @strSPText + CHAR(13) + 'END'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
print '-------------------------------------------------'
Print @strSPText;
print '-------------------------------------------------'
Exec(@strSPText);


if (@@ERROR=0)
Print 'Procedure ' + @insertSPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @insertSPName + ' Already exists in the database. '
End
/* Create UPDATE stored procedure for the table if it does not exist */
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@updateSPName) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
Begin
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + '/*-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + '-- Author : dbo'
Set @strSPText = @strSPText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + '-- Description : Update Procedure for ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @updateSPName + ' ' + @tableCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @updateSPName


if exists(select * from #tmp_PK_Structure)
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablinsertParameters
else
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParameters
--Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @tablColumnParameters

Set @strSPText = @strSPText + CHAR(13) + 'AS'
Set @strSPText = @strSPText + CHAR(13) + 'BEGIN'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + @space + 'UPDATE [dbo].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space + 'SET '
Set @strSPText = @strSPText + CHAR(13) + @space + @space + ' ' + @updCols
Set @strSPText = @strSPText + CHAR(13) + @space + @whereCols
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + 'END'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''

print '-------------------------------------------------'
Print @strSPText ;
print '-------------------------------------------------'
Exec(@strSPText);

if (@@ERROR=0)
Print 'Procedure ' + @updateSPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @updateSPName + ' Already exists in the database. '
End
/* Create DELETE stored procedure for the table if it does not exist */
IF Not EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@deleteSPName) AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
Begin
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + '/*-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + '-- Author : dbo'
Set @strSPText = @strSPText + CHAR(13) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + '-- Description : Delete Procedure for ' + @tblName
Set @strSPText = @strSPText + CHAR(13) + '-- Exec ' + @deleteSPName + ' ' + @delParamCols
Set @strSPText = @strSPText + CHAR(13) + '-- ============================================= */'
Set @strSPText = @strSPText + CHAR(13) + 'CREATE PROCEDURE ' + @deleteSPName
Set @strSPText = @strSPText + CHAR(13) + @space + ' ' + @delParamCols
Set @strSPText = @strSPText + CHAR(13) + 'AS'
Set @strSPText = @strSPText + CHAR(13) + 'BEGIN'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + @space + 'DELETE FROM [dbo].['+@tblName +']'
Set @strSPText = @strSPText + CHAR(13) + @space + @whereCols
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + 'END'
Set @strSPText = @strSPText + CHAR(13) + ''
Set @strSPText = @strSPText + CHAR(13) + ''
print '-------------------------------------------------'
Print @strSPText ;
print '-------------------------------------------------'
Exec(@strSPText);

if (@@ERROR=0)
Print 'Procedure ' + @deleteSPName + ' Created Successfully '
End
Else
Begin
Print 'Sorry!! ' + @deleteSPName + ' Already exists in the database. '
End
End
Drop table #tmp_Structure
Drop table #tmp_Structure1
Drop table #tmp_PK_Structure
    Fetch next
from Tables_cursor
INTO @Table_Schema,@Table_Name
END
CLOSE Tables_cursor
DEALLOCATE Tables_cursor
END

Answer 1

Thanks for the original script.
I improved it with the following :
- Allow to do on another database
- For select, generate dynamically the query according parameters
- Manage columns CreatedBy/Date and ModificationBy/Date
- Work even if special characters are found in schema/table/column
- Allow to add systematically the user and culture.
- Template for procedure name
And lot of options.

Note: code send in two answer as limited to 30000 characters.

IF OBJECT_ID('dbo.GenerateDynamicallyProceduresForTables','P') IS NOT NULL
    DROP PROCEDURE dbo.GenerateDynamicallyProceduresForTables
GO

CREATE PROCEDURE dbo.GenerateDynamicallyProceduresForTables @DatabaseName nvarchar(200)=NULL,
                                                            @SchemaName nvarchar(200) = NULL,
                                                            @TableName nvarchar(200) = NULL,
                                                            @NoCount bit=1,
                                                            @ManageTransaction bit=1,
                                                            @GenerateDebugScriptForList bit = 1,
                                                            @ParameterForUser nvarchar(20) = '@UserInP',
                                                            @ParameterForCulture nvarchar(20) = '@CultureInP',
                                                            @FirstParametersAreMandatory bit=1,
                                                            @ProcedureTemplateName nvarchar(100) = '[{SchemaName}].[{TableName}_Proc_{ActionType}]',
                                                            @CreationUserMatch nvarchar(500) = 'syscolumns.name LIKE ''%CreationUser%'' OR syscolumns.name LIKE ''%CreationBy%''',
                                                            @CreationDateMatch nvarchar(500) = 'syscolumns.name LIKE ''%CreationDate%'' OR syscolumns.name LIKE ''%CreatedDate%''',
                                                            @ModificationUserMatch nvarchar(500) = 'syscolumns.name LIKE ''%ModificationUser%'' OR syscolumns.name LIKE ''%ModifiedBy%''  OR syscolumns.name LIKE ''%ModifiedUser%''',
                                                            @ModificationDateMatch nvarchar(500) = 'syscolumns.name LIKE ''%ModificationDate%'' OR syscolumns.name LIKE ''%ModifiedDate%'''
AS
BEGIN
DECLARE @UnCommentExecForDebug bit=0 --To set at 0 for final

DECLARE @StatementList TABLE(id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,FullTableName nvarchar(1000),StatementType nvarchar(100),Statement nvarchar(max))  
DECLARE @FirstParameters nvarchar(400)='',@FirstParametersForExec nvarchar(400)=''

IF LEN(@ParameterForUser)>1 
BEGIN
  SET @FirstParameters = @FirstParameters + @ParameterForUser  +' nvarchar(500)' + CASE WHEN @FirstParametersAreMandatory =0  THEN ' = NULL' ELSE '' END +  ', 
    '
  SET @FirstParametersForExec = @FirstParametersForExec + @ParameterForUser + CASE WHEN @FirstParametersAreMandatory =0  THEN ' = NULL' ELSE ' =''K2:Denallix\Administrator''' END +  ', 
    '
END
IF LEN(@ParameterForCulture)>1
BEGIN
  SET @FirstParameters = @FirstParameters + @ParameterForCulture  + ' nvarchar(10)' + CASE WHEN @FirstParametersAreMandatory =0  THEN ' = NULL' ELSE '' END +  ', 
    '
  SET @FirstParametersForExec = @FirstParametersForExec + @ParameterForCulture + CASE WHEN @FirstParametersAreMandatory =0  THEN ' = NULL' ELSE '=''en-gb''' END +  ', 
    '
END
IF NOT(LEN(@DatabaseName)>0)
 SET @DatabaseName=DB_NAME()

 IF LEN(@SchemaName)=0
 SET @SchemaName=NULL

 IF LEN(@TableName)=0
 SET @TableName=NULL

IF NOT(LEN(@CreationUserMatch)>0)  
   SET @CreationUserMatch = 'syscolumns.name = ''BIDON12345678917071979'''

IF NOT(LEN(@CreationDateMatch)>0)  
   SET @CreationDateMatch = 'syscolumns.name = ''BIDON12345678917071979'''

IF NOT(LEN(@ModificationUserMatch)>0)  
   SET @ModificationUserMatch = 'syscolumns.name = ''BIDON12345678917071979'''

IF NOT(LEN(@ModificationDateMatch)>0)  
   SET @ModificationDateMatch = 'syscolumns.name = ''BIDON12345678917071979'''




DECLARE @strSpText nVarchar(max) ='USE [' + @DatabaseName + ']'
IF @DatabaseName!=DB_NAME()
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES ('Common','Set current database',@strSPText)

 DECLARE @sqlstatementForTables nvarchar(max) = -- Not test with USE [' + @DatabaseName + '] ISSUE ON Table iDENTITY.Identity: 'Could not complete cursor operation because the set options have changed since the cursor was declared
      N'
      DECLARE Tables_cursor CURSOR FOR
       SELECT TABLE_SCHEMA,TABLE_NAME
         FROM [' + @DatabaseName + '].INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE=''BASE TABLE''
          AND (TABLE_SCHEMA=@pSchemaName OR @pSchemaName IS NULL)
          AND (Table_Name=@pTableName OR @pTableName IS NULL)'

     --EXEC loopbackServerForDebug.[K2FranceDebugDB].dbo.K2FranceDebug '@sqlstatementForColumns',@sqlstatementForColumns

      exec sp_executesql @sqlstatementForTables, N'@pSchemaName  nvarchar(200),@pTableName  nvarchar(200)', @pSchemaName=@SchemaName, @pTableName=@TableName;

OPEN Tables_cursor
DECLARE @CurrentSchemaName nvarchar(100),@CurrentFullTableName nvarchar(1000),@CurrentTableName nVarchar(1000),
        @DropStatement nvarchar(max)=''
Fetch next
from Tables_cursor
INTO @CurrentSchemaName,@CurrentTableName
WHILE @@FETCH_STATUS=0
BEGIN

    SET @CurrentFullTableName='['+@CurrentSchemaName+'].['+@CurrentTableName+']';
    --PRINT @CurrentFullTableName


    Declare @dbName nVarchar(50)
    Declare @insertSPName nVarchar(4000), @updateSPName nVarchar(4000), @deleteSPName nVarchar(4000), @listSPName nVarchar(4000)--, @ReadSPName nVarchar(50) ;
    Declare @ColumnParametersInsert nVarchar(max), @ColumnDefForInsert nVarchar(max),@ColumnInValueForInsert nVarchar(max),
            @ColumnParametersList nVarchar(max),@ColumnParametersListForExec nVarchar(max),
            @tableColumnForWhereInList nvarchar(max),
            @tableColumnForWhereInListVariables nVarchar(max), @tableColumnForWhereInListAffectVariables nVarchar(max),@DebugVariablesForList nvarchar(max)='',
            @ColumnParametersInsertForExec nvarchar(max)
    Declare @tableCols nVarchar(max), @ColumnParametersUpdate nVarchar(max),@ColumnParametersUpdateForExec nVarchar(max);
    Declare @space nVarchar(50) = REPLICATE(' ', 4) ;
    Declare @colName nVarchar(max) ;
    Declare @DataType nvarchar(200),@colVariable nVarchar(200),@colVariableProc nVarchar(200);
    Declare @colParameter nVarchar(max) ;
    Declare @colAllowNull nvarchar(15), @colIsPrimaryKey INT,@ColIsIdentityAutoIncrement INT,@ColLength INT,@ColIsComputed INT,@ColMatchCreationUser INT,@ColMatchCreationDate INT,@ColMatchModificationUser INT,@ColMatchModificationDate INT;

    Declare @updCols nVarchar(max);
    Declare @ColumnParametersDelete nVarchar(max),@ColumnParametersDeleteForExec nVarchar(max),
            @LastPrimaryKey nvarchar(max),@NbPrimaryKey INT=0,@ColNumber int=0
    Declare @whereCols nVarchar(2000);
    DECLARE @SetVariablesForExec nvarchar(max)='',@SetVariablesForExecUpdate nvarchar(max)='', @SetVariablesForExecDelete nvarchar(max)=''

    DECLARE @strBegin nvarchar(1000)=' AS' + CHAR(13) + CHAR(10) + 'BEGIN',@spaceForTrans nvarchar(10)=''
    IF @NoCount=1
      Set @strBegin = @strBegin + CHAR(13) + CHAR(10) + @space + 'SET NOCOUNT ON '

    IF @ManageTransaction = 1 
    BEGIN
        Set @strBegin = @strBegin + CHAR(13) + CHAR(10) + @space + 'SET XACT_ABORT ON -- if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.'
      Set @strBegin = @strBegin + CHAR(13) + CHAR(10) + ''
      Set @strBegin = @strBegin + CHAR(13) + CHAR(10) + @space + 'BEGIN TRAN '
      SET @spaceForTrans= @space;
    END
    DECLARE @strEnd nvarchar(1000)=''
    IF @ManageTransaction = 1 
      Set @strEnd = @strEnd + CHAR(13) + CHAR(10) + @space + 'COMMIT TRAN '
    SET @strEnd = @strEnd + CHAR(13) + CHAR(10) + 'END'
    Set @strEnd = @strEnd + CHAR(13) + CHAR(10) + 'GO'
    Set @strEnd = @strEnd + CHAR(13) + CHAR(10) + ''
    IF @UnCommentExecForDebug = 0 Set @strEnd = @strEnd + CHAR(13) + CHAR(10) + '/*'

    IF @ProcedureTemplateName IS NULL
      BEGIN
        Set @insertSPName = '['+@CurrentSchemaName+'].[sp_' + @CurrentTableName +'_insert]' ;
        Set @updateSPName = '['+@CurrentSchemaName+'].[sp_' + @CurrentTableName +'_update]' ;
        Set @deleteSPName = '['+@CurrentSchemaName+'].[sp_' + @CurrentTableName +'_delete]' ;
        set @listSPName =  '['+@CurrentSchemaName+'].[sp_' + @CurrentTableName +'_list]' ;
      END
    ELSE
      BEGIN
         DECLARE @ProcedureName nvarchar(200)=REPLACE(REPLACE(@ProcedureTemplateName,'{SchemaName}',ISNULL(@SchemaName,@CurrentSchemaName)),'{TableName}',ISNULL(@TableName,@CurrentTableName));
         Set @insertSPName = REPLACE(@ProcedureName,'{ActionType}','Insert')
         Set @updateSPName = REPLACE(@ProcedureName,'{ActionType}','Update')
         Set @deleteSPName = REPLACE(@ProcedureName,'{ActionType}','Delete')
         Set @listSPName = REPLACE(@ProcedureName,'{ActionType}','List')   
      END

SET @DropStatement = @DropStatement+ '
DROP PROCEDURE ' + @insertSPName + '
DROP PROCEDURE ' + @updateSPName + '
DROP PROCEDURE ' + @deleteSPName +'
DROP PROCEDURE ' + @listSPName 


    Set @ColumnParametersInsert = @FirstParameters ;
    SET @ColumnParametersInsertForExec = @FirstParametersForExec
    Set @ColumnParametersUpdate=@FirstParameters
    SET @ColumnParametersUpdateForExec=@FirstParametersForExec
    Set @ColumnParametersDelete = @FirstParameters ;
    SET @ColumnParametersDeleteForExec = @FirstParametersForExec ;
    SET @ColumnParametersList = @FirstParameters;
    SET @ColumnParametersListForExec = @FirstParametersForExec

    SET @tableColumnForWhereInList= ''
    SET @tableColumnForWhereInListVariables =''
    SET @tableColumnForWhereInListAffectVariables =''
    SET @DebugVariablesForList ='';
    Set @ColumnDefForInsert = '' ;
    Set @ColumnInValueForInsert = '' ;
    Set @strSPText = '' ;
    Set @tableCols = '' ;
    Set @updCols = '' ;

    Set @whereCols = '' ;

    SET NOCOUNT ON


    CREATE TABLE #tmp_Structure (colid int,ColumnName nvarchar(max), 
                                 ColumnVariable nvarchar(max),
                                 DataType nvarchar(max), 
                                 ColumnParameter nvarchar(max), 
                                 AllowNull int, 
                                 IsPrimaryKey int, 
                                 IsIdentityAutoIncrement int, 
                                 ColLength int, 
                                 IsIsComputedColumn int,
                                 ColMatchCreationUser int,ColMatchCreationDate int,
                                 ColMatchModificationUser INT,ColMatchModificationDate INT)

    DECLARE @sqlstatementForColumns nvarchar(max) = 
      N'USE [' + @DatabaseName + ']
      SELECT distinct
           --sysobjects.name as ''Table'',
           syscolumns.colid ,
           ''['' + syscolumns.name + '']'' as ''ColumnName'',
           ''@''+syscolumns.name as ''ColumnVariable'',           
           systypes.name +
           Case When systypes.xusertype in (165,167,175,231,239 ) Then ''('' + Convert(varchar(10),Case When syscolumns.length=-1 Then ''max'' else CAST(syscolumns.length AS nvarchar(10)) end) +'')'' Else '''' end as ''DataType'' ,
           systypes.name +  Case When systypes.xusertype in (165,167,175,231,239 ) Then ''('' + Convert(varchar(10),Case When syscolumns.length=-1 Then ''max'' else CAST(syscolumns.length AS nvarchar(10)) end) +'')'' Else '''' end as ''ColumnParameter'',
           COLUMNPROPERTY(OBJECT_ID(@pFullTableName),syscolumns.name,''AllowsNull'') AS AllowNull,
           /*CASE WHEN syscolumns.name IN (SELECT c.name AS ColumnName
                                             FROM [' + @DatabaseName + '].sys.columns AS c
                                                  INNER JOIN [' + @DatabaseName + '].sys.tables AS t ON t.[object_id] = c.[object_id]
                                            where c.is_identity = 1
                                              and t.name=@CurrentTableName) THEN ''1'' ELSE  ''0'' END IsPrimaryKey,
           */
           (SELECT COUNT(*) 
                    FROM [' + @DatabaseName + '].INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
                         [' + @DatabaseName + '].INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
                   WHERE Col.Constraint_Name = Tab.Constraint_Name
                    AND Col.Table_Name = Tab.Table_Name
                    AND Constraint_Type = ''PRIMARY KEY''
                    AND Col.Table_Name = @pTableName
                    AND Tab.TABLE_SCHEMA=@pSchemaName
                    AND Col.Column_Name = syscolumns.name
                        ) AS IsPrimaryKey, 
           SC.is_identity AS IsIdentityAutoIncrement,
           syscolumns.length,
           (SELECT COUNT(*)
              FROM sys.computed_columns
             WHERE computed_columns.object_id=sysobjects.id
               AND computed_columns.Name=syscolumns.name) AS IsComputedColumn,
            CASE WHEN ' + @CreationUserMatch +' THEN 1 ELSE 0 END AS ColMatchCreationUser,
            CASE WHEN ' + @CreationDateMatch +' THEN 1 ELSE 0 END AS ColMatchCreationDate,
            CASE WHEN ' + @ModificationUserMatch +' THEN 1 ELSE 0 END AS ColMatchModificationUser,
            CASE WHEN ' + @ModificationDateMatch +' THEN 1 ELSE 0 END AS ColMatchModificationDate
    FROM sysobjects 
         LEFT JOIN syscolumns ON syscolumns.id=sysobjects.id
         LEFT JOIN systypes ON systypes.xusertype=syscolumns.xusertype
         LEFT JOIN sys.columns SC ON SC.object_id = sysobjects.id
                           AND SC.name=syscolumns.name
    Where sysobjects.xtype = ''u''
      and sysobjects.id = OBJECT_ID(@pFullTableName)
    Order by syscolumns.colid'

     --PRINT @sqlstatementForColumns
     --EXEC loopbackServerForDebug.[K2FranceDebugDB].dbo.K2FranceDebug '@sqlstatementForColumns',@sqlstatementForColumns

      INSERT INTO #tmp_Structure
      exec sp_executesql @sqlstatementForColumns, N'@pSchemaName  nvarchar(200),@pTableName  nvarchar(200),@pFullTableName nvarchar(1000)', @pSchemaName=@CurrentSchemaName, @pTableName=@CurrentTableName,@pFullTableName=@CurrentFullTableName;


      --SELECT * FROM #tmp_Structure

    /* Read the table structure and populate variables*/
    DECLARE SpText_Cursor CURSOR FOR
     SELECT ColumnName, ColumnVariable, DataType, ColumnParameter, AllowNull, IsPrimaryKey, IsIdentityAutoIncrement,ColLength, IsIsComputedColumn,ColMatchCreationUser,ColMatchCreationDate,ColMatchModificationUser,ColMatchModificationDate
       FROM #tmp_Structure
    OPEN SpText_Cursor

    FETCH NEXT FROM SpText_Cursor INTO @colName, @colVariable,  @DataType, @colParameter, @colAllowNull,@colIsPrimaryKey, @ColIsIdentityAutoIncrement,@ColLength, @ColIsComputed,@ColMatchCreationUser,@ColMatchCreationDate,@ColMatchModificationUser,@ColMatchModificationDate
    WHILE @@FETCH_STATUS = 0
    BEGIN
       SET @ColNumber=@ColNumber+1

       SET @SetVariablesForExec = @SetVariablesForExec  + CASE WHEN @colAllowNull =1 THEN '' 
                                                         ELSE  CASE WHEN @DataType  IN ('datetime','datetime2','smalldatetime','date') AND @SetVariablesForExec NOT LIKE '%@Date%' THEN CHAR(13) +CHAR(10) + 'DECLARE @Date datetime =GetDate()'  
                                                                            WHEN @DataType  IN ('uniqueidentifier') AND @SetVariablesForExec NOT LIKE '%@GuidTest%' THEN CHAR(13) +CHAR(10) + 'DECLARE @TheGuid uniqueidentifier  =NEWID()'                                                                             
                                                                            ELSE '' 
                                                                       END
                                                         END

       --RegEx to keep only alphanumeric characters:
       DECLARE @MatchExpression nvarchar(20) =  '%[^a-z0-9]%',@DateTypeWithoutSpecialCharacters nvarchar(100)=@DataType;

       WHILE PatIndex(@MatchExpression, @DateTypeWithoutSpecialCharacters) > 0
        SET @DateTypeWithoutSpecialCharacters = Stuff(@DateTypeWithoutSpecialCharacters, PatIndex(@MatchExpression, @DateTypeWithoutSpecialCharacters), 1, '')


       --Remove Special characters (like space...) for variable name
       WHILE PatIndex(@MatchExpression, @colVariable) > 0
        SET @colVariable = Stuff(@colVariable, PatIndex(@MatchExpression, @colVariable), 1, '')

       SET @colVariableProc = '@p'+ @colVariable 
       SET @colVariable = '@'+ @colVariable 

       SET @colParameter = @colVariable + ' ' + @colParameter 






       DECLARE @AffectationForExec nvarchar(max)=@colVariable + CASE WHEN @colAllowNull =1 THEN ' = NULL' 
                     ELSE ' = ' +  CASE WHEN @DataType IN ('Text','sysname') OR @DataType LIKE '%char%'  THEN '''' + SUBSTRING ( CAST(ABS(@ColLength) AS nvarchar(10)) + 'TEST' + @DateTypeWithoutSpecialCharacters,0,CASE WHEN @ColLength < 0 THEN 1000 WHEN @DataType LIKE 'nchar%' THEN @ColLength/2+1 ELSE @ColLength END) + ''''
                                        WHEN @DataType  IN ('int','numeric','bigint','tinyint') THEN CAST(@ColNumber AS nvarchar(10))   
                                        WHEN @DataType  IN ('bit') THEN '0'   
                                        WHEN @DataType  IN ('float') THEN CAST(@ColNumber AS nvarchar(10))   +  '.' + CAST(@ColNumber+1 AS nvarchar(10)) 
                                        WHEN @DataType  IN ('datetime','datetime2','smalldatetime','date') THEN '@Date'  
                                        WHEN @DataType  IN ('uniqueidentifier') THEN '@TheGuid'  
                                        WHEN @DataType  IN ('xml') THEN '''<testXML><value name="test">' + CAST(@ColNumber AS nvarchar(10)) + '</value></testXML>''' 
                                        ELSE '''1''--Currently Not managed' 
                                   END
                END +  ', --Type ' + @DataType  + CHAR(13) + CHAR(10) + @space 

       IF @ColIsIdentityAutoIncrement = 0 AND @ColIsComputed = 0 
       BEGIN
          IF @ColMatchModificationUser = 0  AND @ColMatchModificationDate = 0
            Set @ColumnDefForInsert = @ColumnDefForInsert + @colName+ ',' + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans ;

          IF @ColMatchCreationUser= 0 AND @ColMatchCreationDate = 0 AND @ColMatchModificationUser = 0  AND @ColMatchModificationDate = 0
          BEGIN
            Set @ColumnParametersInsert = @ColumnParametersInsert + @colParameter + CASE WHEN @colAllowNull =1  THEN ' = NULL' ELSE '' END +  ','  + CHAR(13) + CHAR(10) + @space ;
            SET @ColumnParametersInsertForExec = @ColumnParametersInsertForExec + @AffectationForExec
          END 

          IF @ColMatchCreationUser= 1
          BEGIN
            IF LEN(@ParameterForUser)>1 
               Set @ColumnInValueForInsert = @ColumnInValueForInsert + 'ISNULL(' + @ParameterForUser + ',SYSTEM_USER)'
            ELSE
               Set @ColumnInValueForInsert = @ColumnInValueForInsert + 'SYSTEM_USER'
          END
          ELSE
          BEGIN
            IF @ColMatchCreationDate= 1
              Set @ColumnInValueForInsert = @ColumnInValueForInsert + 'GETDATE()'  
            ELSE
              IF @ColMatchModificationUser = 0  AND @ColMatchModificationDate = 0
                Set @ColumnInValueForInsert = @ColumnInValueForInsert + @colVariable
          END
          IF @ColMatchCreationUser= 1 OR @ColMatchCreationDate= 1 OR @ColMatchModificationUser = 0  AND @ColMatchModificationDate = 0
        SET @ColumnInValueForInsert =@ColumnInValueForInsert + ',' + CHAR(13) + CHAR(10) + @space + @space+ @spaceForTrans


          Set @tableCols = @tableCols + @colName + ',' ;


          IF @ColMatchModificationUser = 1
          BEGIN
             IF LEN(@ParameterForUser)>1 
               Set @updCols = @updCols + @colName + ' = ISNULL(' + @ParameterForUser + ',SYSTEM_USER)'; 
            ELSE
               Set @updCols = @updCols + @colName + ' = SYSTEM_USER'; 
          END 
          ELSE
          BEGIN
            IF @ColMatchModificationDate = 1
               Set @updCols = @updCols + @colName + ' = GETDATE()';     
            ELSE
              IF @ColMatchCreationUser=0 AND @ColMatchCreationDate=0
              Set @updCols = @updCols + @colName + ' = ' + @colVariable;     
          END   
          IF @ColMatchModificationUser = 1 OR @ColMatchModificationDate = 1 OR @ColMatchCreationUser=0 AND @ColMatchCreationDate=0
        SET @updCols =@updCols + ',' + CHAR(13) + CHAR(10) + @space + @space+ '   ' + @spaceForTrans


       END

       SET @ColumnParametersList = @ColumnParametersList + @colParameter + ' = NULL' +  ','  + CHAR(13) + CHAR(10) + @space ;
       SET @ColumnParametersListForExec = @ColumnParametersListForExec+  @colVariable + ' = NULL, --Type ' + @DataType  + CHAR(13) + CHAR(10) + @space 

       IF @ColIsIdentityAutoIncrement = 1 AND @DataType='int'
          BEGIN
            SET @SetVariablesForExecUpdate =   CHAR(13)+CHAR(10)+'DECLARE @PrimaryKeyValue INT= (SELECT MIN(' + @colName + ') FROM ' + @CurrentFullTableName + ')'
            SET @AffectationForExec = @colVariable  + '= @PrimaryKeyValue, --Type ' + @DataType  + CHAR(13) + CHAR(10) + @space 
          END 

       IF @ColIsComputed = 0 AND @ColMatchCreationUser=0 AND @ColMatchCreationDate=0 AND @ColMatchModificationUser=0 AND @ColMatchModificationDate=0
       BEGIN
         Set @ColumnParametersUpdate = @ColumnParametersUpdate + @colParameter + ',' + CHAR(13) + CHAR(10) + @space ;
         SET @ColumnParametersUpdateForExec = @ColumnParametersUpdateForExec + @AffectationForExec
       END

       IF @DataType NOT IN ('text') 
       BEGIN
         IF @DataType NOT IN ('Xml')
         BEGIN
             SET @tableColumnForWhereInList = @tableColumnForWhereInList + '
                 IF ' + @colVariable + ' IS NOT NULL
                   BEGIN
                     SET @Statement= @Statement+ @Separator + ''' + REPLACE(@colName,'''','''''') + '= '+  @colVariableProc +'''
                     SET @Separator = @SeparatorAnd
                   END'
             SET @tableColumnForWhereInListVariables = @tableColumnForWhereInListVariables + @space + @space + @space + @spaceForTrans + @colVariableProc + ' ' + @DataType +',
        '
             SET @tableColumnForWhereInListAffectVariables = @tableColumnForWhereInListAffectVariables + @space + @space + @space  + @spaceForTrans + @colVariableProc + '=' + @colVariable + ',
        '             
         END  
          SET @DebugVariablesForList = @DebugVariablesForList+ CHAR(13) +CHAR(10)  + @space + @space + @space + @space +@space+ @spaceForTrans 
          IF @DataType IN ('Xml')
            SET @DebugVariablesForList = @DebugVariablesForList+  'ISNULL(''DECLARE '+ @colVariableProc + ' ' + @DataType+' = CAST('''''' + REPLACE(CAST(' +@colVariable + ' as nvarchar(max)),'''''''','''''''''''') + ''''''AS XML);''+CHAR(13)+CHAR(10) ,'''') + '
          ELSE
            SET @DebugVariablesForList = @DebugVariablesForList+  'ISNULL(''DECLARE '+ @colVariableProc + ' ' + @DataType+' = '''''' + REPLACE(' +@colVariable + ','''''''','''''''''''') + '''''';''+CHAR(13)+CHAR(10) ,'''') + '
       END


       IF @colIsPrimaryKey= 1
       BEGIN
          IF @ColIsIdentityAutoIncrement = 1 AND @DataType='int'
          BEGIN
            SET @SetVariablesForExecDelete =   CHAR(13)+CHAR(10)+'DECLARE @PrimaryKeyValue INT= (SELECT MAX(' + @colName + ') FROM ' + @CurrentFullTableName + ')'
          END 

         SET @ColumnParametersDelete = @ColumnParametersDelete + @colParameter +', ' + CHAR(13) + CHAR(10) + @space ;

         SET @ColumnParametersDeleteForExec = @ColumnParametersDeleteForExec + @AffectationForExec
         SET @whereCols = @whereCols + @colName + ' = ' + @colVariable + ' AND ' ;
         SET @NbPrimaryKey = @NbPrimaryKey +1
         SET @LastPrimaryKey = @colName
       END
    FETCH NEXT FROM SpText_Cursor INTO @colName, @colVariable, @DataType,@colParameter, @colAllowNull,@colIsPrimaryKey,@ColIsIdentityAutoIncrement,@ColLength,@ColIsComputed,@ColMatchCreationUser,@ColMatchCreationDate,@ColMatchModificationUser,@ColMatchModificationDate
    END
    CLOSE SpText_Cursor
    DEALLOCATE SpText_Cursor

    IF @ColumnDefForInsert IS NULL
      RAISERROR('@ColumnDefForInsert IS NULL',16,1)
    IF @ColumnParametersInsert IS NULL
      RAISERROR('@ColumnParametersInsert IS NULL',16,1)
    IF @ColumnParametersInsertForExec IS NULL
      RAISERROR('@ColumnParametersInsertForExec IS NULL',16,1)
    IF @ColumnInValueForInsert IS NULL
      RAISERROR('@ColumnInValueForInsert IS NULL',16,1)
    IF @tableCols IS NULL
      RAISERROR('@tableCols IS NULL',16,1)
    IF @updCols IS NULL
      RAISERROR('@updCols IS NULL',16,1)

    IF @ColumnParametersDelete IS NULL
      RAISERROR('@ColumnParametersDelete IS NULL',16,1)
    IF @whereCols IS NULL
      RAISERROR('@whereCols IS NULL',16,1)

    DECLARE @LastPosOfComma INT

    If (LEN(@ColumnParametersUpdate)>0)
    BEGIN
      Set @ColumnParametersUpdate = LEFT(@ColumnParametersUpdate,LEN(@ColumnParametersUpdate)-3) ;
      SET @LastPosOfComma = LEN(@ColumnParametersUpdateForExec) - CHARINDEX(' ,',REVERSE(@ColumnParametersUpdateForExec))
      SET @ColumnParametersUpdateForExec = LEFT(@ColumnParametersUpdateForExec,@LastPosOfComma+3) + SUBSTRING(@ColumnParametersUpdateForExec,@LastPosOfComma+5,40000);
    END
     --See next post for the end of procedure

Answer 2

Here the end of the procedure:

If (LEN(@ColumnParametersInsert)>0)
    Begin


      Set @ColumnParametersInsert = LEFT(@ColumnParametersInsert,LEN(@ColumnParametersInsert)-3) ;
      SET @LastPosOfComma = LEN(@ColumnParametersInsertForExec) - CHARINDEX(' ,',REVERSE(@ColumnParametersInsertForExec))
      SET @ColumnParametersInsertForExec = LEFT(@ColumnParametersInsertForExec,@LastPosOfComma+3) + SUBSTRING(@ColumnParametersInsertForExec,@LastPosOfComma+5,40000);

      Set @ColumnParametersDelete = LEFT(@ColumnParametersDelete,LEN(@ColumnParametersDelete)-4) ;
      SET @LastPosOfComma = LEN(@ColumnParametersDeleteForExec) - CHARINDEX(' ,',REVERSE(@ColumnParametersDeleteForExec))
      SET @ColumnParametersDeleteForExec = LEFT(@ColumnParametersDeleteForExec,@LastPosOfComma+3) + SUBSTRING(@ColumnParametersDeleteForExec,@LastPosOfComma+5,40000);



      SET @ColumnParametersList = LEFT(@ColumnParametersList,LEN(@ColumnParametersList)-3) ;
      SET @LastPosOfComma = LEN(@ColumnParametersListForExec) - CHARINDEX(' ,',REVERSE(@ColumnParametersListForExec))
      SET @ColumnParametersListForExec = LEFT(@ColumnParametersListForExec,@LastPosOfComma+3) + SUBSTRING(@ColumnParametersListForExec,@LastPosOfComma+5,40000);

      IF LEN(@ColumnInValueForInsert)>0
        Set @ColumnInValueForInsert = LEFT(@ColumnInValueForInsert,LEN(@ColumnInValueForInsert)-3) ;
      IF LEN(@ColumnDefForInsert)>0
        Set @ColumnDefForInsert = LEFT(@ColumnDefForInsert,LEN(@ColumnDefForInsert)-3) ;
      IF LEN(@tableCols)>0
        Set @tableCols = LEFT(@tableCols,LEN(@tableCols)-1) ;
      IF LEN(@updCols)>0
        Set @updCols = LEFT(@updCols,LEN(@updCols)-3) ;
      SET @tableColumnForWhereInListVariables = LEFT(@tableColumnForWhereInListVariables,LEN(@tableColumnForWhereInListVariables)-3)
      SET @tableColumnForWhereInListAffectVariables = LEFT(@tableColumnForWhereInListAffectVariables,LEN(@tableColumnForWhereInListAffectVariables)-3) ;


    END


    If (LEN(@whereCols)>0)
      Set @whereCols = 'WHERE ' + LEFT(@whereCols,LEN(@whereCols)-4) ;
    ELSE
       Set @whereCols = 'WHERE 1=0 --Too dangerous to do update or delete on all the table'

    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Author : ' + SYSTEM_USER
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Create date : ' + Convert(varchar(20),Getdate())
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Description : Insert Procedure for ' + @CurrentTableName    
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='

    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'IF OBJECT_ID(''' + REPLACE(@insertSPName,'''','''''') + ''',''P'') IS NOT NULL'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '   DROP PROCEDURE  ' + @insertSPName 
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''

    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'CREATE PROCEDURE ' + @insertSPName
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + '' + @ColumnParametersInsert   
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strBegin
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + 'INSERT INTO ' + @CurrentFullTableName + '('
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + '    ' + '' + @ColumnDefForInsert + ')'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + 'VALUES ('
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + '    ' + '' + @ColumnInValueForInsert + ')'

    IF @NbPrimaryKey =1 --No return if 2 or 0 primarykeys 
      Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + 'SELECT SCOPE_IDENTITY() AS ' + @LastPrimaryKey

    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strEnd

    Set @strSPText = @strSPText + @SetVariablesForExec
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'EXEC ' + @insertSPName + ' '  + @ColumnParametersInsertForExec
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'SELECT * FROM ' + @CurrentFullTableName + ' ORDER BY 1 DESC'
    IF @UnCommentExecForDebug = 0 Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '*/'

    INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES (@CurrentFullTableName,'Insert',@strSPText)



    Set @strSPText = ''
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Author : ' + SYSTEM_USER
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Create date : ' + Convert(varchar(20),Getdate())
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Description : Update Procedure for ' + @CurrentTableName
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='

    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'IF OBJECT_ID(''' + REPLACE(@updateSPName,'''','''''') + ''',''P'') IS NOT NULL'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '   DROP PROCEDURE  ' + @updateSPName 
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''


    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'CREATE PROCEDURE ' + @updateSPName
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + '' + @ColumnParametersUpdate
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strBegin
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans+ 'UPDATE ' + @CurrentFullTableName 
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans+ '   SET ' + @updCols
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans+ ' ' + @whereCols
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strEnd
    Set @strSPText = @strSPText + @SetVariablesForExec  + @SetVariablesForExecUpdate  
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'EXEC ' + @updateSPName + ' ' + @ColumnParametersUpdateForExec
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'SELECT * FROM ' + @CurrentFullTableName + ' '
    IF @UnCommentExecForDebug = 0 Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '*/'

    INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES (@CurrentFullTableName,'Update',@strSPText)

    Set @strSPText = ''
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Author : ' + SYSTEM_USER
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Create date : ' + Convert(varchar(20),Getdate())
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Description : Delete Procedure for ' + @CurrentTableName
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='

    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'IF OBJECT_ID(''' + REPLACE(@deleteSPName,'''','''''') + ''',''P'') IS NOT NULL'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '   DROP PROCEDURE  ' + @deleteSPName 
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''

    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'CREATE PROCEDURE ' + @deleteSPName
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + '' + @ColumnParametersDelete
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strBegin
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + 'DELETE FROM ' + @CurrentFullTableName
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + ' ' + @whereCols
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strEnd
    Set @strSPText = @strSPText + @SetVariablesForExecDelete
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'EXEC ' + @deleteSPName + ' ' + @ColumnParametersDeleteForExec
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'SELECT * FROM ' + @CurrentFullTableName + ' ORDER BY 1 DESC'
    IF @UnCommentExecForDebug = 0 Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '*/'

    INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES (@CurrentFullTableName,'Delete',@strSPText)





    Set @strSPText = ''
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Author : ' + SYSTEM_USER
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Create date : ' + Convert(varchar(20),Getdate())
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Description : List Procedure for ' + @CurrentFullTableName
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='

    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'IF OBJECT_ID(''' + REPLACE(@listSPName,'''','''''') + ''',''P'') IS NOT NULL'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '   DROP PROCEDURE  ' + @listSPName 
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''


    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'CREATE PROCEDURE ' + @listSPName
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + '' + @ColumnParametersList
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strBegin
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space +'   DECLARE @Separator  nvarchar(20) =''
     WHERE '''
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space +'   DECLARE @SeparatorAnd  nvarchar(20) =''
       AND '''
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space +'   DECLARE @Statement nvarchar(max) =''SELECT * 
      FROM ' + REPLACE(@CurrentFullTableName,'''','''''') + '''' + @tableColumnForWhereInList

    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + ' --PRINT @Statement'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + ' BEGIN TRY'

    IF @GenerateDebugScriptForList=1
    BEGIN
      Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + '    IF 1=0--DEBUG --TODO: verify if not set for final version'
      Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + '      BEGIN'
      Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + '          DECLARE @FullQueryForDebug nvarchar(max)=' + @DebugVariablesForList
      Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + '            CHAR(13) +CHAR(10) + @Statement'
      Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + '  '
      Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + '         --PRINT @FullQueryForDebug'
      Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + '         --EXEC [K2FranceDebugDB].dbo.K2FranceDebug  ''@FullQueryForDebug DIRECT'', @FullQueryForDebug'
      Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + '         --EXEC loopbackServerForDebug.[K2FranceDebugDB].dbo.K2FranceDebug  ''@FullQueryForDebug loopback'', @FullQueryForDebug'
      Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + '      END'
    END
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + '     exec sp_executesql @Statement ,N''' + @tableColumnForWhereInListVariables + ''',' + @tableColumnForWhereInListAffectVariables
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + ' END TRY'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + ' BEGIN CATCH'

    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + '   DECLARE @ErrorToDisplay nvarchar(max)= ''Error trying to execute Query Error number:'' + CAST(ERROR_NUMBER() AS nvarchar(max)) + 
                        --'' Error severity:'' + ISNULL(CAST(ERROR_SEVERITY() AS nvarchar(max)),'''') + 
                        --'' Error state:'' + ISNULL(CAST(ERROR_STATE() AS nvarchar(max)),'''')  + 
                        --'' Error procedure:'' + ISNULL(CAST(ERROR_PROCEDURE() AS nvarchar(max)),'''')  + 
                        --'' Error line:'' + ISNULL(CAST(ERROR_LINE() AS nvarchar(max)),'''')  + 
                        '' Error message:'' + ISNULL(CAST(ERROR_MESSAGE() AS nvarchar(max)),'''')
'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + '   RAISERROR(@ErrorToDisplay, 16, 1);'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space  + @spaceForTrans + ' END CATCH'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strEnd
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'EXEC ' + @listSPName + ' ' + @ColumnParametersListForExec
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
    Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'SELECT * FROM ' + @CurrentFullTableName + ' ORDER BY 1'
    IF @UnCommentExecForDebug = 0 Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '*/'


    INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES (@CurrentFullTableName,'List',@strSPText)




    Drop table #tmp_Structure   
    Fetch next from Tables_cursor INTO @CurrentSchemaName,@CurrentTableName
END
CLOSE Tables_cursor
DEALLOCATE Tables_cursor



SET @DropStatement = '

------------------------------------------- TO CLEAN COMPLETELY THE APPLICATION ---------------------------------------
/*' + @DropStatement +'
*/' 
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES ('Common','Drop statement to put at the end of final script',@DropStatement)


SELECT * FROM @StatementList
ORDER BY 1


END
GO

--For all tables of schema dbo of database "OlivierDb":
EXEC dbo.GenerateDynamicallyProceduresForTables 'OlivierDB','dbo' 

--With all possible parameters:
EXEC dbo.GenerateDynamicallyProceduresForTables @DatabaseName               = 'OlivierDB',
                                                @SchemaName                 = 'dbo',
                                                @TableName                  = 'Table5',
                                                @NoCount                    = 1,
                                                @ManageTransaction          = 1,
                                                @GenerateDebugScriptForList = 1,
                                                @ParameterForUser           = '@UserInP',
                                                @ParameterForCulture        = '@CultureInP',
                                                @FirstParametersAreMandatory= 1,
                                                @ProcedureTemplateName      = '[{SchemaName}].[{TableName}_Proc_{ActionType}]',
                                                @CreationUserMatch          = 'syscolumns.name LIKE ''%CreationUser%'' OR syscolumns.name LIKE ''%CreationBy%''',
                                                @CreationDateMatch          = 'syscolumns.name LIKE ''%CreationDate%'' OR syscolumns.name LIKE ''%CreatedDate%''',
                                                @ModificationUserMatch      = 'syscolumns.name LIKE ''%ModificationUser%'' OR syscolumns.name LIKE ''%ModifiedBy%''  OR syscolumns.name LIKE ''%ModifiedUser%''',
                                                @ModificationDateMatch      = 'syscolumns.name LIKE ''%ModificationDate%'' OR syscolumns.name LIKE ''%ModifiedDate%'''

Answer 3

replace USE [AdventureWorks2012] with db name that will use.

I have tested it, stored procedures for all tables were created.

Answer 4

ssms tool pack contains a tool to do the same thing (perfectly).

Right click on a table, SSMS Tools, Create CRUD. It doesn't answer your question correctly ("why doesn't this work") if you want to learn from your question but saves you reinventing the wheel if you just want to get it working.