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.

 

LISTAGEM 01 – SCRIPT PARA GERAR PROCEDURES DE UPDATE

 

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

 

 

      LISTAGEM 02 – UMA DAS PROCEDURES GERADAS

 

--------------------------------------------------------------------------------

-- TABELA FILME ---> ALTERAÇÃO DE REGISTROS

--------------------------------------------------------------------------------

-- INICIO DA PROCEDURE ---------------------------------------------------------

--------------------------------------------------------------------------------

 

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[_SP_FILME_ALTERAR]') AND OBJECTPROPERTY(ID, N'ISPROCEDURE') = 1)

DROP PROCEDURE [_SP_FILME_ALTERAR]

 

GO

 

CREATE PROC _SP_FILME_ALTERAR

--------------------------------------------------------------------------------

--

-- PROCEDURE _SP_FILME_ALTERAR

-- GERADA AUTOMATICAMENTE

-- EM 2006-07-24 10:03:54

-- ### AGNALDO DIOGO DOS SANTOS ###

--

-- ALTERAÇÃO DE REGISTRO NA TABELA FILME POR ID

--

--------------------------------------------------------------------------------

 

@COD_FILME               INT,                                                                                                   

@NOM_FILME               VARCHAR(50),                                                                                                  

@NOM_ORIGINAL_FILME      VARCHAR(50) = NULL,                                                                                            

@ANO_FILME               INT = NULL                                                                                            

 

AS

 

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

 IF EXISTS(SELECT 1 FROM FILME WHERE NOM_FILME = @NOM_FILME AND COD_FILME <> @COD_FILME)

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

 ELSE

 BEGIN

 UPDATE FILME SET

 NOM_FILME = @NOM_FILME,

 NOM_ORIGINAL_FILME = @NOM_ORIGINAL_FILME,

 ANO_FILME = @ANO_FILME

 WHERE

 COD_FILME = @COD_FILME

 

 IF @@ERROR<>0

 RAISERROR('ERRO: A ALTERAÇÃO NA TABELA [FILME] NÃO PODE SER FEITA - ERRO NA PROCEDURE [_SP_FILME_ALTERAR]', 16, 1)

 END

 

GO

 

--------------------------------------------------------------------------------

-- FIM DA PROCEDURE ------------------------------------------------------------

--------------------------------------------------------------------------------

 

 

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.

Veja a primeira etapa deste artigo em:

http://www.devmedia.com.br/articles/visualizacomponente2.asp?comp=2207

Veja a segunda parte deste artigo em:
http://www.devmedia.com.br/articles/visualizacomponente2.asp?comp=2261