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_FILMEINT,
@NOM_FILMEVARCHAR(50),
@NOM_ORIGINAL_FILMEVARCHAR(50) = NULL,
@ANO_FILMEINT = 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:
https://www.devmedia.com.br/articles/visualizacomponente2.asp?comp=2207
Veja a segunda parte deste artigo em:
https://www.devmedia.com.br/articles/visualizacomponente2.asp?comp=2261