SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Author          Rui Borges
-- Create date: Create Date     2017-01-20
-- Description: Description     Metadados-Criação de SPs de manipulação de tabela
-- =============================================

alter PROCEDURE sp_generate_sql_tabela_update (
    @tabela        nvarchar(128)
)
AS
    declare @tab_c  nvarchar(128);
    declare @col_t  nvarchar(128);
    declare @col_l  nvarchar(128);
    declare @col_p  nvarchar(128);
    declare @pos    int = 0;
    declare @pks    int = 0;
    declare @crlf   nchar(2) = CHAR(13) + CHAR(10);


    DECLARE @sp_header         NVARCHAR(MAX) = '';
    DECLARE @sp_header_action  NVARCHAR(500) = '';
    DECLARE @sp_columns        NVARCHAR(MAX) = '';
    DECLARE @sp_args           NVARCHAR(MAX) = '';
    DECLARE @sp_update         NVARCHAR(MAX) = '';
    DECLARE @sp_where          NVARCHAR(MAX) = '';
    DECLARE @sp_key            NVARCHAR(128) = '';
    DECLARE @sp_linha          NVARCHAR(500) = '';
    DECLARE @sp_all            NVARCHAR(MAX) = '';

    -- Determinar a primary key
    SELECT @pks = count(*)
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
        WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
        AND A.TABLE_NAME = @tabela;

    if @pks < 1
        begin
            raiserror (50011, 16, -1, '@sp_key', 'sp_generate_sql_tabela_update')
            return (1)
        end

    SELECT @sp_header = '-- ============================================= ' + @crlf
                      + '-- Author:        Author           Rui Borges    ' + @crlf
                      + '-- Create date:   Create Date  ' + REPLACE(CONVERT(CHAR(10), CONVERT (date, GETDATE()), 103), '-','') +@crlf
                      + '-- Description:   Description  Metadados - Criação de SPs de manipulação de tabela ' + @crlf
                      + '-- ============================================= '  + @crlf;

    SELECT @sp_header_action = '-- U P D A T E '  + @crlf
                             + 'CREATE PROCEDURE [dbo].sp_' + @tabela + '_update ('  + @crlf;

    SELECT @sp_update =  @crlf +  @crlf + 'UPDATE [dbo].' + @tabela + ' SET '+ @crlf;

    SELECT @sp_where =  @crlf  + '   WHERE ' + @crlf;

    select @sp_columns = @sp_header + @sp_header_action;


BEGIN
    SET NOCOUNT ON;

    -- Cursor para obter informação sobre as colunas da tabela para os argumentos
    DECLARE #curInfoTab CURSOR LOCAL FAST_FORWARD FOR
        SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION
            FROM testedb.INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @tabela;

    OPEN #curInfoTab

    FETCH #curInfoTab INTO @tab_c, @col_t, @col_l, @col_p

    WHILE (@@FETCH_STATUS = 0)
    BEGIN

        if @pos > 0
          begin
              set @sp_linha = ', ' + @crlf;
          end;

        -- Argumentos
        set @sp_linha = @sp_linha + '     @p_' + @tab_c + '     ' + @col_t ;
        if @col_t = 'nchar' or @col_t = 'nvarchar' or @col_t = 'varchar'
              set @sp_linha = @sp_linha + '(' + @col_l + ')';

        set  @sp_args = @sp_args + @sp_linha;

        set @pos = @pos + 1;

        FETCH #curInfoTab INTO @tab_c, @col_t, @col_l, @col_p
    END
    CLOSE #curInfoTab;
    DEALLOCATE #curInfoTab;

    -- Cursor para obter informação sobre as colunas da tabela para o update
    DECLARE #curInfoCol CURSOR LOCAL FAST_FORWARD FOR
        SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @tabela
              AND COLUMN_NAME not in (
                    SELECT B.COLUMN_NAME
                      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
                     WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
                       AND A.TABLE_NAME = @tabela
                );

    set @pos = 0;

    OPEN #curInfoCol

    FETCH #curInfoCol INTO @tab_c, @col_t, @col_l, @col_p

    WHILE (@@FETCH_STATUS = 0)
    BEGIN

        if @pos > 0
          begin
              set @sp_update = @sp_update + ', ' + @crlf;
          end;

        -- Campos no comando Update
        set @sp_update = @sp_update + '    ' + @tab_c + ' = @p_' + @tab_c;

        set @pos = @pos + 1;

        FETCH #curInfoCol INTO @tab_c, @col_t, @col_l, @col_p
    END
    CLOSE #curInfoCol;
    DEALLOCATE #curInfoCol;

    -- Cursor para obter informação sobre as colunas chave da tabela
    DECLARE #curInfoKey CURSOR LOCAL FAST_FORWARD FOR
        SELECT  COLUMN_NAME
                FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
                WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
                AND A.TABLE_NAME = @tabela;

    set @pos = 0;

    OPEN #curInfoKey

    FETCH #curInfoKey INTO @tab_c

    WHILE (@@FETCH_STATUS = 0)
    BEGIN

        if @pos > 0
          begin
              set @sp_where = @sp_where + ', ' + @crlf;
          end;

        -- Campos da clausula where
        set @sp_where = @sp_where + '    ' + @tab_c + ' = @p_' + @tab_c;

        set @pos = @pos + 1;

        FETCH #curInfoKey INTO @tab_c
    END
    CLOSE #curInfoKey;
    DEALLOCATE #curInfoKey;

    set @sp_args   = @sp_args + @crlf  + '  ) ' + @crlf + '  AS ' + @crlf;
    set @sp_all    = @sp_columns + @sp_args + @sp_update +  @crlf;
    set @sp_all    = @sp_all + @sp_where +  @crlf;
    set @sp_all    = @sp_all + 'COMMIT;  '  + @crlf;

    --print @sp_all;
    exec(@sp_all);

END
GO