Gerando procedures automaticamente no SQL 2000

PARTE 03/05 – Script gerador de procedures de atualização de dados

Agora, já com as tabelas criadas, use o script da listagem 01 para criar procedures de UPDATE para todas as tabelas do seu database.

SET NOCOUNT ON

DECLARE @ID INT, @NAME VARCHAR(255)

DECLARE @CAMPO VARCHAR(255), @ORDEM INT, @VARIAVEL CHAR(25), @TIPO VARCHAR(25), @NULO CHAR(100), @VIRGULA VARCHAR(1)

DECLARE TABELA CURSOR FOR

SELECT ID, NAME

FROM SYSOBJECTS

WHERE TYPE = 'U'

AND STATUS>1000000000

OPEN TABELA

FETCH NEXT FROM TABELA INTO @ID, @NAME

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT ''

PRINT ''

PRINT ''

PRINT '--------------------------------------------------------------------------------'

PRINT '-- TABELA ' + RTRIM(@NAME) + ' ---> ALTERAÇÃO DE REGISTROS'

PRINT '--------------------------------------------------------------------------------'

PRINT '-- INICIO DA PROCEDURE ---------------------------------------------------------'

PRINT '--------------------------------------------------------------------------------'

PRINT ''

PRINT 'IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N''[_SP_' + @NAME + '_ALTERAR]'') AND OBJECTPROPERTY(ID, N''ISPROCEDURE'') = 1)'

PRINT 'DROP PROCEDURE [_SP_' + @NAME + '_ALTERAR]'

PRINT ''

PRINT 'GO'

PRINT ''

PRINT 'CREATE PROC _SP_' + @NAME + '_ALTERAR '

PRINT '--------------------------------------------------------------------------------'

PRINT '-- '

PRINT '-- PROCEDURE _SP_' + @NAME + '_ALTERAR'

PRINT '-- GERADA AUTOMATICAMENTE'

PRINT '-- EM ' + CONVERT(VARCHAR,GETDATE(),120)

PRINT '-- ### AGNALDO DIOGO DOS SANTOS ###'

PRINT '-- '

PRINT '-- ALTERAÇÃO DE REGISTRO NA TABELA ' + @NAME + ' POR ID'

PRINT '-- '

PRINT '--------------------------------------------------------------------------------'

PRINT ''

DECLARE CAMPO SCROLL CURSOR FOR

SELECT NAME

FROM SYSCOLUMNS

WHERE ID = @ID

AND NAME<>'DAT_INC'

AND NAME<>'DAT_ALT'

AND NAME<>'DAT_EXC'

AND NAME<>'USU_INC'

AND NAME<>'USU_ALT'

AND NAME<>'USU_EXC'

ORDER BY COLORDER

OPEN CAMPO

FETCH NEXT FROM CAMPO INTO @CAMPO

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT

@VARIAVEL = '@' + CONVERT(CHAR(25), T1.NAME),

@TIPO =

CASE T2.NAME

WHEN 'VARCHAR' THEN CONVERT(VARCHAR, 'VARCHAR(' + CONVERT(VARCHAR(5), T1.LENGTH) + ')')

WHEN 'CHAR' THEN CONVERT(VARCHAR, 'CHAR(' + CONVERT(VARCHAR(5), T1.LENGTH) + ')')

WHEN 'NCHAR' THEN CONVERT(VARCHAR, 'NCHAR(' + CONVERT(VARCHAR(5), T1.LENGTH/2) + ')')

WHEN 'INT' THEN CONVERT(VARCHAR, 'INT')

WHEN 'BIT' THEN CONVERT(VARCHAR, 'BIT')

WHEN 'NVARCHAR' THEN CONVERT(VARCHAR, 'NVARCHAR(' + CONVERT(VARCHAR(5), T1.LENGTH/2) + ')')

WHEN 'DATETIME' THEN CONVERT(VARCHAR, 'DATETIME')

WHEN 'MONEY' THEN CONVERT(VARCHAR, 'MONEY')

WHEN 'TINYINT' THEN CONVERT(VARCHAR, 'TINYINT')

WHEN 'TEXT' THEN CONVERT(VARCHAR, 'TEXT')

WHEN 'NTEXT' THEN CONVERT(VARCHAR, 'NTEXT')

WHEN 'NUMERIC' THEN CONVERT(VARCHAR, 'NUMERIC(' + CONVERT(VARCHAR(2), T1.XPREC) + ',' + CONVERT(VARCHAR(2), T1.XSCALE) + ')')

WHEN 'FLOAT' THEN CONVERT(VARCHAR, 'FLOAT')

END,

@NULO =

CASE ISNULLABLE

WHEN 1 THEN ' = NULL'

WHEN 0 THEN ''

END

+

CASE

WHEN COLORDER <> (SELECT MAX(COLORDER) FROM SYSCOLUMNS WHERE ID = @ID AND NAME<>'DAT_INC' AND NAME<>'DAT_ALT' AND NAME<>'DAT_EXC' AND NAME<>'USU_INC' AND NAME<>'USU_ALT' AND NAME<>'USU_EXC' )

THEN ', '

ELSE

''

END

FROM

SYSCOLUMNS T1,

SYSTYPES T2

WHERE

T1.XTYPE = T2.XTYPE

AND

OBJECT_NAME(ID) = @NAME

AND

T2.NAME != 'SYSNAME'

AND

T1.NAME = @CAMPO

ORDER BY

COLORDER

PRINT @VARIAVEL + @TIPO + @NULO

FETCH NEXT FROM CAMPO INTO @CAMPO

END

CLOSE CAMPO

DEALLOCATE CAMPO

PRINT ''

PRINT 'AS'

PRINT ''

PRINT 'SET NOCOUNT ON'

PRINT 'SET ANSI_WARNINGS OFF'

IF EXISTS(SELECT 1 FROM SYSCOLUMNS WHERE ID=@ID AND NAME = 'DSC_' + @NAME)

BEGIN

PRINT ''

PRINT 'IF EXISTS(SELECT 1 FROM ' + @NAME + ' WHERE DSC_'+ @NAME + ' = @DSC_' + @NAME +' AND COD_' + @NAME + ' <> @COD_' + @NAME + ')'

PRINT ' RAISERROR(''ALERTA: A ALTERAÇÃO NA TABELA [' + @NAME + '] NÃO PODE SER FEITA - O REGISTRO JÁ EXISTE NO BANCO'', 16, 1)'

PRINT 'ELSE'

PRINT ' BEGIN'

END

IF EXISTS(SELECT 1 FROM SYSCOLUMNS WHERE ID=@ID AND NAME = 'NOM_' + @NAME)

BEGIN

PRINT ' IF EXISTS(SELECT 1 FROM ' + @NAME + ' WHERE NOM_'+ @NAME + ' = @NOM_' + @NAME +' AND COD_' + @NAME + ' <> @COD_' + @NAME + ')'

PRINT ' RAISERROR(''ALERTA: A ALTERAÇÃO NA TABELA [' + @NAME + '] NÃO PODE SER FEITA - O REGISTRO JÁ EXISTE NO BANCO'', 16, 1)'

PRINT ' ELSE'

PRINT ' BEGIN'

END

PRINT ' UPDATE ' + @NAME + ' SET'

DECLARE CAMPO SCROLL CURSOR FOR

SELECT NAME, COLORDER

FROM SYSCOLUMNS

WHERE ID = @ID

AND STATUS != 128

AND NAME<>'DAT_INC'

AND NAME<>'DAT_ALT'

AND NAME<>'DAT_EXC'

AND NAME<>'USU_INC'

AND NAME<>'USU_ALT'

AND NAME<>'USU_EXC'

ORDER BY COLORDER

OPEN CAMPO

FETCH NEXT FROM CAMPO INTO @CAMPO, @ORDEM

WHILE @@FETCH_STATUS = 0

BEGIN

IF @ORDEM != (SELECT MAX(COLORDER) FROM SYSCOLUMNS WHERE ID = @ID

AND STATUS != 128

AND NAME<>'DAT_INC'

AND NAME<>'DAT_ALT'

AND NAME<>'DAT_EXC'

AND NAME<>'USU_INC'

AND NAME<>'USU_ALT'

AND NAME<>'USU_EXC' )

SET @VIRGULA = ', '

ELSE

IF EXISTS (SELECT 1 FROM SYSCOLUMNS WHERE NAME = 'DAT_ALT' AND ID = @ID)

SET @VIRGULA = ', '

ELSE

SET @VIRGULA = ' '

PRINT ' ' + @CAMPO + ' = @' + @CAMPO + @VIRGULA

FETCH NEXT FROM CAMPO INTO @CAMPO, @ORDEM

END

CLOSE CAMPO

DEALLOCATE CAMPO

IF EXISTS (SELECT 1 FROM SYSCOLUMNS WHERE NAME = 'DAT_ALT' AND ID = @ID)

BEGIN

PRINT ' DAT_ALT = GETDATE(), '

PRINT ' USU_ALT = HOST_NAME() + '' - '' + APP_NAME() + '' - '' + USER_NAME()'

END

SELECT @CAMPO = NAME FROM SYSCOLUMNS WHERE ID = @ID AND STATUS=128

PRINT ' WHERE '

PRINT ' ' + @CAMPO + ' = @' + @CAMPO

PRINT ''

PRINT ' IF @@ERROR<>0'

PRINT ' RAISERROR(''ERRO: A ALTERAÇÃO NA TABELA [' + @NAME + '] NÃO PODE SER FEITA - ERRO NA PROCEDURE [_SP_' + @NAME + '_ALTERAR]'', 16, 1)'

IF EXISTS(SELECT 1 FROM SYSCOLUMNS WHERE ID=@ID AND NAME = 'NOM_' + @NAME)

PRINT ' END'

IF EXISTS(SELECT 1 FROM SYSCOLUMNS WHERE ID=@ID AND NAME = 'DSC_' + @NAME)

PRINT ' END'

PRINT ''

PRINT 'GO'

PRINT ''

PRINT '--------------------------------------------------------------------------------'

PRINT '-- FIM DA PROCEDURE ------------------------------------------------------------'

PRINT '--------------------------------------------------------------------------------'

PRINT ''

PRINT ''

PRINT ''

FETCH NEXT FROM TABELA INTO @ID, @NAME

END

CLOSE TABELA

DEALLOCATE TABELA

Aguarde as próximas duas matérias, onde mostrarei a criação automática das procedures de deleção (lógica ou física, dependendo da tabela) e de seleção de registros. Até mais.