如何从SQL Server Management Studio中的表生成CRUD存储过程

sql sql-server tsql ssms crud

26847 观看

7回复

5033 作者的声誉

如何在SSMS中为表格自动生成CRUD存储过程?

作者: The Internet 的来源 发布者: 2012 年 7 月 26 日

回应 (7)


20

206108 作者的声誉

决定

SSMS无法生成CRUD过程。您可以通过右键单击Script Table As来生成INSERT,UPDATE语句等,但我认为您将有更好的运气与Mladen Prajdic的SSMS工具包

作者: Aaron Bertrand 发布者: 26.07.2012 04:15

20

19082 作者的声誉

如果您使用的是Visual Studio,则可以执行以下操作:http//weblogs.asp.net/stevewellens/archive/2009/12/11/automatically-generate-stored-procedures-with-visual-studio.aspx

作者: Steve Wellens 发布者: 26.07.2012 04:24

15

673 作者的声誉

我有一个简单的TSQL脚本我用来做我的。它是基本的,但很容易修改,以满足您的需求。它使用您指定的表和视图为Upsert,Select和Delete Procedure生成TSQL。

    /*
This is a simple, single-table CRUD Generator. It does not have a bunch of 
bells and whistles, and is easy to follow and modify. I wrote this to make
my job easier, and I am sharing it with you to do with it as you wish.

The Basics:
The TSQL below will create 3 procedures:
    1. An Upsert Procedure: Suffix _ups 
    2. A Select Procedure: Suffix _sel
    3. A Delete Procedure: Suffix _del

A Little More Detail:
Things you should know:
    All 3 procedures have a parameter called @MyID which is used to set 
    the Context, so that my audit procedures get the validated user. If you
    Have no use for it, you'll need to remove the piece of generator code 
    that adds it as a parameter to each of the 3 procedures. You will also
    need to remove the PRINT statement for each procedure that looks like:

            PRINT N'  SET CONTEXT_INFO @MyID;' + CHAR(13) + CHAR(10) 

    This generator expects to perform inserts, updates, and deletes on a
    table, and selects from a view. If you want to perform selects directly
    from the table, simply use the table name in both @TableName and 
    @ViewName.

The Upsert Procedure:
    If ID (Primary Key) is supplied it will perform an Update. Otherwise it 
    will perform an Insert. This generator is hard-coded to avoid inserting
    or updating these particular fields:
                                        Created
                                        CreatedBy
                                        Modified
                                        ModifiedBy
                                        RowVersion
                                        <The Primary Key Field>
    That's because in my databases I use those field names for audit, and they
    are never modified except internally within the database. You can modify
    the part of this procedure that performs this function to suit your needs.

    This generator always uses the Parameter name @ID to represent the Primary
    key defined for the table. This is my preference but you can modify to suit.

The Select Procedure:
    If ID (Primary Key) is supplied it will select a single row from the View 
    (Table) whose name you provide. Otherwise it will select all rows. If the 
    @ISACTIVE_SEL variable is set to 1 (True), then the Select Procedure expects
    your View (Table) to have a bit-type field named 'IsActive'. My tables are 
    standardized to this. If @ISACTIVE_SEL = 1 the Select Procedure will have an
    additional parameter called @IsActive (bit). When @ID is not supplied, and 
    @IsActive is not supplied, the procedure selects all rows. When @ID is not
    supplied, and @IsActive is supplied, the procedure selects all rows where
    the field IsActive matches the parameter @IsActive

The Delete Procedure:
    The Delete Pocedure requires that the Key value and the RowVersion value
    be supplied. I use an Int type RowVersion, so if you use TimeStamp (varbinary(128))
    then you will need to tweak the generator.


    --Casey W Little
    --Kaciree Software Solutions, LLC
    Version 1.00
*/


--Type Your Database Name in this Use statement:
USE [<Your Database>]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*MODIFY THE VALUES BELOW TO SUIT YOUR NEEDS*/
DECLARE @DBName nvarchar(100)=N'<Your Database>';
DECLARE @ProcName nvarchar(100)=N'<Your Proc Name>';
DECLARE @DBRoleName nvarchar(100)=N'<Role that should have exec Rights>';
DECLARE @TableName nvarchar(100)=N'<Your Table Name>';
DECLARE @ViewName nvarchar(100)=N'<Your View Name>';
DECLARE @OrderBy nvarchar(100)=N'<Your Field Name>';
DECLARE @OrderByDir nvarchar(4)=N'ASC';
DECLARE @AUTHOR nvarchar(50) ='<Your Name & Company>';
DECLARE @DESC nvarchar(100) ='<Proc Information>'; -- Ex. 'User Data' will return 'Description : Upsert User Data'
DECLARE @ISACTIVE_SEL bit =0; --Set to 1 if your table has a Bit field named IsActive
/*DO NOT MODIFY BELOW THIS LINE!!!*/

DECLARE @NNND char(23) ='NOT_NULLABLE_NO_DEFAULT';
DECLARE @NNWD char(22) ='NOT_NULLABLE_W_DEFAULT';
DECLARE @NBLE char(8) ='NULLABLE';
DECLARE @LEGEND nvarchar(max);
DECLARE @PRIMARY_KEY nvarchar(100);


--Set up Legend
    SET @LEGEND = N'USE [' + @DBName + N'];' + CHAR(13) + CHAR(10) 
    SET @LEGEND = @LEGEND + N'GO' + CHAR(13) + CHAR(10) 
    SET @LEGEND = @LEGEND + CHAR(13) + CHAR(10) 
    SET @LEGEND = @LEGEND + N'SET ANSI_NULLS ON' + CHAR(13) + CHAR(10) 
    SET @LEGEND = @LEGEND + N'GO' + CHAR(13) + CHAR(10) 
    SET @LEGEND = @LEGEND + CHAR(13) + CHAR(10) 
    SET @LEGEND = @LEGEND + N'SET QUOTED_IDENTIFIER ON' + CHAR(13) + CHAR(10) 
    SET @LEGEND = @LEGEND + N'GO' + CHAR(13) + CHAR(10) 
    SET @LEGEND = @LEGEND + CHAR(13) + CHAR(10) 

    SET @LEGEND = @LEGEND + N'-- ===================================================================' + CHAR(13) + CHAR(10) 
    SET @LEGEND = @LEGEND + N'-- Author      : ' + @AUTHOR + CHAR(13) + CHAR(10) 
    SET @LEGEND = @LEGEND + N'-- Create date : ' + CONVERT(nvarchar(30),GETDATE(),101) + CHAR(13) + CHAR(10) 
    SET @LEGEND = @LEGEND + N'-- Revised date: ' + CHAR(13) + CHAR(10) 

--Get Primary Key Field
SELECT TOP 1 @PRIMARY_KEY = COLUMN_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND TABLE_NAME = @TableName AND TABLE_CATALOG = @DBName;

DECLARE TableCol Cursor FOR 
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH
    , IIF(c.COLUMN_NAME='RowVersion',@NBLE,IIF(c.COLUMN_NAME=@PRIMARY_KEY,@NBLE,IIF(c.IS_NULLABLE = 'NO' AND c.COLUMN_DEFAULT IS NULL,@NNND,IIF(c.IS_NULLABLE = 'NO' AND c.COLUMN_DEFAULT IS NOT NULL,@NNWD,@NBLE)))) AS [NULLABLE_TYPE]
FROM INFORMATION_SCHEMA.Columns c INNER JOIN
    INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.Table_Catalog = @DBName
    AND t.TABLE_TYPE = 'BASE TABLE'
    AND t.TABLE_NAME = @TableName
ORDER BY [NULLABLE_TYPE], c.ORDINAL_POSITION;

DECLARE @TableSchema varchar(100), @cTableName varchar(100), @ColumnName varchar(100);
DECLARE @DataType varchar(30), @CharLength int, @NullableType varchar(30);

DECLARE @PARAMETERS nvarchar(max);
DECLARE @INSERT_FIELDS nvarchar(max),@INSERT_VALUES nvarchar(max);
DECLARE @UPDATE_VALUES nvarchar(max);

SET @PARAMETERS ='@MyID int,';
SET @INSERT_FIELDS ='';
SET @INSERT_VALUES ='';
SET @UPDATE_VALUES ='';

-- open the cursor
OPEN TableCol

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

WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @ColumnName NOT IN('Created','CreatedBy','Modified','ModifiedBy')
        BEGIN
            SET @PARAMETERS=@PARAMETERS + '@' + IIF(@ColumnName=@PRIMARY_KEY,'ID',@ColumnName) + ' ' + iif(@CharLength IS NULL,@DataType,@DataType + '(' + 
                CAST(@CharLength AS nvarchar(10)) + ')') +  IIF(@NullableType=@NNND OR @NullableType=@NNWD,',','=NULL,');
            IF @ColumnName <> @PRIMARY_KEY AND @ColumnName <> N'RowVersion'
                BEGIN
                    SET @INSERT_FIELDS=@INSERT_FIELDS + '[' + @ColumnName + '],';
                    SET @INSERT_VALUES=@INSERT_VALUES + '@' + IIF(@ColumnName=@PRIMARY_KEY,'ID',@ColumnName) + ',';
                    SET @UPDATE_VALUES=@UPDATE_VALUES + '[' + @ColumnName + ']=@' + IIF(@ColumnName=@PRIMARY_KEY,'ID',@ColumnName) + ',';
                END
        END

        FETCH NEXT FROM TableCol INTO @TableSchema, @cTableName, @ColumnName, @DataType, @CharLength, @NullableType
    END;

    SET @PARAMETERS=LEFT(@PARAMETERS,LEN(@PARAMETERS)-1)
    SET @INSERT_FIELDS=LEFT(@INSERT_FIELDS,LEN(@INSERT_FIELDS)-1)
    SET @INSERT_VALUES=LEFT(@INSERT_VALUES,LEN(@INSERT_VALUES)-1)
    SET @UPDATE_VALUES=LEFT(@UPDATE_VALUES,LEN(@UPDATE_VALUES)-1)

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

--Print Upsert Statement
    PRINT N'/****** Object:  StoredProcedure [dbo].[' + @ProcName + '_ups]    Script Date: ' + CAST(GETDATE() AS nvarchar(30)) + '  ******/' + CHAR(13) + CHAR(10) 
    PRINT @LEGEND;
    PRINT N'-- Description : Upsert ' + @DESC + CHAR(13) + CHAR(10) 
    PRINT N'-- ===================================================================' + CHAR(13) + CHAR(10) 
    PRINT CHAR(13) + CHAR(10) 
    PRINT N'CREATE PROCEDURE [dbo].[' + @ProcName  + '_ups]' + CHAR(13) + CHAR(10);
    PRINT N'  (' + @PARAMETERS + N')' + CHAR(13) + CHAR(10);
    PRINT N'AS' + CHAR(13) + CHAR(10) 
    PRINT N'BEGIN' + CHAR(13) + CHAR(10) 
    PRINT N'  SET CONTEXT_INFO @MyID;' + CHAR(13) + CHAR(10) 
    PRINT N'  IF @ID IS NULL OR @ID = 0' + CHAR(13) + CHAR(10) 
    PRINT N'    BEGIN' + CHAR(13) + CHAR(10) 
    PRINT N'      INSERT INTO [dbo].[' + @TableName + ']' + CHAR(13) + CHAR(10) 
    PRINT N'        (' + @INSERT_FIELDS + N')' + CHAR(13) + CHAR(10) 
    PRINT N'      VALUES' + CHAR(13) + CHAR(10) 
    PRINT N'        (' + @INSERT_VALUES + N');' + CHAR(13) + CHAR(10) 
    PRINT N'      SELECT * FROM [dbo].[' + @ViewName + '] WHERE [ID] = SCOPE_IDENTITY();' + CHAR(13) + CHAR(10) 
    PRINT N'    END' + CHAR(13) + CHAR(10) 
    PRINT N'  ELSE' + CHAR(13) + CHAR(10) 
    PRINT N'    BEGIN' + CHAR(13) + CHAR(10) 
    PRINT N'      UPDATE [dbo].[' + @TableName + ']' + CHAR(13) + CHAR(10) 
    PRINT N'        SET ' + @UPDATE_VALUES + CHAR(13) + CHAR(10) 
    PRINT N'        WHERE ([' + @PRIMARY_KEY + '] = @ID) AND ([RowVersion] = @RowVersion);' + CHAR(13) + CHAR(10) 
    PRINT N'      SELECT * FROM [dbo].[' + @ViewName + '] WHERE [ID] = @ID;' + CHAR(13) + CHAR(10) 
    PRINT N'    END' + CHAR(13) + CHAR(10) 
    PRINT N'END' + CHAR(13) + CHAR(10) 
    PRINT N'GO' + CHAR(13) + CHAR(10) 
    PRINT CHAR(13) + CHAR(10) 

----Now add GRANT and DENY permissions to the Role
    PRINT N'GRANT EXECUTE ON [dbo].[' + @ProcName + '_ups] TO [' + @DBRoleName + ']' + CHAR(13) + CHAR(10) 
    PRINT N'GO' + CHAR(13) + CHAR(10) 
    PRINT N'DENY VIEW DEFINITION ON [dbo].[' + @ProcName + '_ups] TO [' + @DBRoleName + ']' + CHAR(13) + CHAR(10) 
    PRINT N'GO' + CHAR(13) + CHAR(10) 
    PRINT CHAR(13) + CHAR(10) 
    PRINT CHAR(13) + CHAR(10) 

    --Print Select Statement
    PRINT N'/****** Object:  StoredProcedure [dbo].[' + @ProcName + '_sel]    Script Date: ' + CAST(GETDATE() AS nvarchar(30)) + '  ******/' + CHAR(13) + CHAR(10) 
    PRINT @LEGEND;
    PRINT N'-- Description : Select ' + @DESC + CHAR(13) + CHAR(10) 
    PRINT N'-- ===================================================================' + CHAR(13) + CHAR(10) 
    PRINT CHAR(13) + CHAR(10) 
    PRINT N'CREATE PROCEDURE [dbo].[' + @ProcName  + '_sel]' + CHAR(13) + CHAR(10);
    PRINT N'  (@MyID int, @ID int=NULL' + IIF(@ISACTIVE_SEL = 1,', @IsActive bit=NULL','') + ')' + CHAR(13) + CHAR(10);
    PRINT N'AS' + CHAR(13) + CHAR(10) 
    PRINT N'BEGIN' + CHAR(13) + CHAR(10) 
    PRINT N'  SET CONTEXT_INFO @MyID;' + CHAR(13) + CHAR(10) 
    PRINT N'  IF @ID IS NULL OR @ID = 0' + CHAR(13) + CHAR(10) 

    IF @ISACTIVE_SEL = 1
        BEGIN
            PRINT N'    BEGIN' + CHAR(13) + CHAR(10) 
            PRINT N'      IF @IsActive IS NULL' + CHAR(13) + CHAR(10) 
            PRINT N'        SELECT * FROM [dbo].[' + @ViewName + '] ORDER BY [' + @OrderBy + '] ' + @OrderByDir + ';' + CHAR(13) + CHAR(10) 
            PRINT N'      ELSE' + CHAR(13) + CHAR(10) 
            PRINT N'        SELECT * FROM [dbo].[' + @ViewName + '] WHERE [isActive] = @IsActive ORDER BY [' + @OrderBy + '] ' + @OrderByDir + ';' + CHAR(13) + CHAR(10) 
            PRINT N'    END' + CHAR(13) + CHAR(10) 
        END
    ELSE
        PRINT N'    SELECT * FROM [dbo].[' + @ViewName + '] ORDER BY [' + @OrderBy + '] ' + @OrderByDir + ';' + CHAR(13) + CHAR(10) 

    PRINT N'  ELSE' + CHAR(13) + CHAR(10) 
    PRINT N'    SELECT * FROM [dbo].[' + @ViewName + '] WHERE [ID] = @ID;' + CHAR(13) + CHAR(10) 
    PRINT N'END' + CHAR(13) + CHAR(10) 
    PRINT N'GO' + CHAR(13) + CHAR(10) 
    PRINT CHAR(13) + CHAR(10) 

----Now add GRANT and DENY permissions to the Role
    PRINT N'GRANT EXECUTE ON [dbo].[' + @ProcName + '_sel] TO [' + @DBRoleName + ']' + CHAR(13) + CHAR(10) 
    PRINT N'GO' + CHAR(13) + CHAR(10) 
    PRINT N'DENY VIEW DEFINITION ON [dbo].[' + @ProcName +'_sel] TO [' + @DBRoleName + ']' + CHAR(13) + CHAR(10) 
    PRINT N'GO' + CHAR(13) + CHAR(10) 
    PRINT CHAR(13) + CHAR(10) 
    PRINT CHAR(13) + CHAR(10) 

    --Print Delete Statement
    PRINT N'/****** Object:  StoredProcedure [dbo].[' + @ProcName + '_del]    Script Date: ' + CAST(GETDATE() AS nvarchar(30)) + '  ******/' + CHAR(13) + CHAR(10) 
    PRINT @LEGEND;
    PRINT N'-- Description : Delete ' + @DESC + CHAR(13) + CHAR(10) 
    PRINT N'-- ===================================================================' + CHAR(13) + CHAR(10) 
    PRINT CHAR(13) + CHAR(10) 
    PRINT N'CREATE PROCEDURE [dbo].[' + @ProcName  + '_del]' + CHAR(13) + CHAR(10);
    PRINT N'  (@MyID int, @ID int, @RowVersion int)' + CHAR(13) + CHAR(10);
    PRINT N'AS' + CHAR(13) + CHAR(10) 
    PRINT N'BEGIN' + CHAR(13) + CHAR(10) 
    PRINT N'  SET CONTEXT_INFO @MyID;' + CHAR(13) + CHAR(10) 
    PRINT N'  SET NOCOUNT ON;' + CHAR(13) + CHAR(10) 
    PRINT N'  DELETE FROM [dbo].[' + @TableName + '] WHERE [' + @PRIMARY_KEY + ']=@ID AND [RowVersion]=@RowVersion;' + CHAR(13) + CHAR(10) 
    PRINT N'  SELECT @@ROWCOUNT as [Rows Affected];' + CHAR(13) + CHAR(10) 
    PRINT N'END' + CHAR(13) + CHAR(10) 
    PRINT N'GO' + CHAR(13) + CHAR(10) 
    PRINT CHAR(13) + CHAR(10) 

----Now add GRANT and DENY permissions to the Role
    PRINT N'GRANT EXECUTE ON [dbo].[' + @ProcName + '_del] TO [' + @DBRoleName + ']' + CHAR(13) + CHAR(10) 
    PRINT N'GO' + CHAR(13) + CHAR(10) 
    PRINT N'DENY VIEW DEFINITION ON [dbo].[' + @ProcName +'_del] TO [' + @DBRoleName + ']' + CHAR(13) + CHAR(10) 
    PRINT N'GO' + CHAR(13) + CHAR(10) 
作者: KacireeSoftware 发布者: 10.07.2013 08:30

13

179 作者的声誉

除了前面提到的工具之外,您还可以使用另一个免费工具,只需点击几下即可完成工作。

为此,您需要在ApexSQL Complete选项窗口中输入前缀和后缀,您可以在其中为每个CRUD过程模板(选择,插入,更新,删除)选择一个子选项卡。完成此操作后,右键单击对象资源管理器窗口,在下拉菜单中的数据库或表格中,可以使用CRUD过程功能。

下面是一个  文章  有关于这个功能的更多详细资料(文章是有点老了,虽然,作为特征被添加到当前版本)

作者: V.Jokinen 发布者: 27.06.2017 08:09

0

1 作者的声誉

CREATE PROCEDURE SP_USUARIO
@usuario varchar(30)=null,
@clave varchar(500)=null,
@idPersona int=null,
@idRol int=null,
@idUsuario int=null,
@TIPO TINYINT=1
AS
BEGIN
    IF @TIPO=1 -- LISTAR PERSONAS
    BEGIN
        SELECT idPersona,Nombre,ApePaterno,ApeMaterno,DNI,Direccion,celular,email FROM Persona
    END
    IF @TIPO=2 -- LISTAR ROLES
    BEGIN
        SELECT idRol,Nombre,Descripcion FROM ROL
    END
    IF @TIPO=3 -- LISTAR USUARIOS
    BEGIN
        SELECT idUsuario,usuario,pass,idPersona,idRol from Usuario
    END
    IF @TIPO=3 -- USUARIOS
    BEGIN
        SELECT idUsuario,usuario,pass,idPersona,idRol from Usuario where idUsuario=@idUsuario
    END
    IF @TIPO=4 -- AGREGAR USUARIO
    BEGIN
        INSERT Usuario(usuario,pass,idPersona,idRol) values(@usuario,@clave,@idPersona,@idRol)
    END
    IF @TIPO=5 -- EDITAR USUARIO
    BEGIN
        UPDATE Usuario SET usuario=@usuario,pass=@clave,@idPersona=@idPersona,idRol=@idRol where idUsuario=@idUsuario
    END
    IF @TIPO=6 -- ELIMINAR USUARIO
    BEGIN
        DELETE Usuario WHERE idUsuario=@idUsuario
    END
END

go
作者: user9817675 发布者: 20.05.2018 03:26

0

1 作者的声誉

谢谢你的原始剧本。
我改进了以下内容:
- 允许在另一个数据库上执行
- 对于select,根据参数动态生成查询
- 管理列CreatedBy / Date和ModificationBy / Date
- 即使在schema / table / column中找到特殊字符也可以工作
- 允许系统地添加用户和文化。
- 程序名称模板
和许多选项。

注意:代码以两个答案发送,限制为30000个字符。

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}]',
                                                            @ColumnNameLimitation nvarchar(500)= '', --(syscolumns.name LIKE ''%Creation%'' OR syscolumns.name IN (''SomeInt'',''Somebit'') )
                                                            @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(@ColumnNameLimitation)>0)  
  SET @ColumnNameLimitation = '1=1'

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,
           (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)
      AND (' + @ColumnNameLimitation + ')
    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
          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   
          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
作者: Olivier Chatagnon 发布者: 03.12.2018 09:25

0

1 作者的声誉

-- Here end end of the procedure (started on another post)
    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}]',
                                                @ColumnNameLimitation       = '', --(syscolumns.name LIKE ''%Creation%'' OR syscolumns.name IN (''SomeInt'',''Somebit'') )
                                                @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%'''
作者: Olivier Chatagnon 发布者: 03.12.2018 09:32
32x32