Gerando procedures automaticamente no SQL 2000
PARTE 02/05 – Script gerador de procedures de inserção de dados
Agora, já com as tabelas criadas, use o script da listagem 01 para criar procedures de INSERT para todas as tabelas do seu database.
LISTAGEM 01 – SCRIPT PARA GERAR PROCEDURES DE INSERT
SET NOCOUNT ON
DECLARE @ID INT, @NAME VARCHAR(255)
DECLARE @CAMPO VARCHAR(255), @ORDEM INT, @VARIAVEL CHAR(25), @TIPO VARCHAR(25), @NULO CHAR(10), @VIRGULA VARCHAR(1)
DECLARE TABELA CURSOR FOR
SELECT ID, NAME
FROM SYSOBJECTS
WHERE TYPE = 'U'
AND STATUS>0
OPEN TABELA
FETCH NEXT FROM TABELA INTO @ID, @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '--------------------------------------------------------------------------------'
PRINT '-- TABELA ' + RTRIM(@NAME) + ' ---> INCLUSÃO DE REGISTROS'
PRINT '--------------------------------------------------------------------------------'
PRINT '-- INICIO DA PROCEDURE ---------------------------------------------------------'
PRINT '--------------------------------------------------------------------------------'
PRINT ''
PRINT 'IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N''[_SP_' + @NAME + '_INCLUIR]'') AND OBJECTPROPERTY(ID, N''ISPROCEDURE'') = 1)'
PRINT 'DROP PROCEDURE [_SP_' + @NAME + '_INCLUIR]'
PRINT ''
PRINT 'GO'
PRINT ''
PRINT 'CREATE PROC _SP_' + @NAME + '_INCLUIR '
PRINT '--------------------------------------------------------------------------------'
PRINT '-- '
PRINT '-- PROCEDURE _SP_' + @NAME + '_INCLUIR'
PRINT '-- GERADA AUTOMATICAMENTE'
PRINT '-- EM ' + CONVERT(VARCHAR,GETDATE(),120)
PRINT '-- ### AGNALDO DIOGO DOS SANTOS ###'
PRINT '-- '
PRINT '-- INCLUSÃO NA TABELA ' + @NAME
PRINT '-- '
PRINT '--------------------------------------------------------------------------------'
PRINT ''
DECLARE CAMPO SCROLL CURSOR FOR
SELECT NAME
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
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
WHEN ISNULLABLE = 1 THEN CONVERT(VARCHAR, ' = NULL')
WHEN ISNULLABLE = 0 THEN CONVERT(VARCHAR, '')
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
T1.STATUS != 128
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 +')'
PRINT ' RAISERROR(''ALERTA: A INCLUSÃ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 ''
PRINT ' IF EXISTS(SELECT 1 FROM ' + @NAME + ' WHERE NOM_'+ @NAME + ' = @NOM_' + @NAME +')'
PRINT ' RAISERROR(''ALERTA: A INCLUSÃO NA TABELA [' + @NAME + '] NÃO PODE SER FEITA - O REGISTRO JÁ EXISTE NO BANCO'', 16, 1)'
PRINT ' ELSE'
PRINT ' BEGIN'
END
PRINT ' INSERT INTO ' + @NAME + ' ('
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
SELECT @VIRGULA = CASE WHEN @ORDEM <> (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
PRINT ' ' + @CAMPO + @VIRGULA
FETCH NEXT FROM CAMPO INTO @CAMPO, @ORDEM
END
CLOSE CAMPO
DEALLOCATE CAMPO
PRINT ' )'
PRINT ' VALUES ('
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
SELECT @VIRGULA = CASE WHEN @ORDEM <> (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
PRINT ' @' + @CAMPO + @VIRGULA
FETCH NEXT FROM CAMPO INTO @CAMPO, @ORDEM
END
CLOSE CAMPO
DEALLOCATE CAMPO
PRINT ' )'
PRINT ' IF @@ERROR<>0'
PRINT ' RAISERROR(''ERRO: A INCLUSÃO NA TABELA [' + @NAME + '] NÃO PODE SER FEITA - ERRO NA PROCEDURE [_SP_' + @NAME + '_INCLUIR]'', 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 ---> INCLUSÃO DE REGISTROS
--------------------------------------------------------------------------------
-- INICIO DA PROCEDURE ---------------------------------------------------------
--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[_SP_FILME_INCLUIR]') AND OBJECTPROPERTY(ID, N'ISPROCEDURE') = 1)
DROP PROCEDURE [_SP_FILME_INCLUIR]
GO
CREATE PROC _SP_FILME_INCLUIR
--------------------------------------------------------------------------------
--
-- PROCEDURE _SP_FILME_INCLUIR
-- GERADA AUTOMATICAMENTE
-- EM 2006-07-06 15:33:54
-- ### AGNALDO DIOGO DOS SANTOS ###
--
-- INCLUSÃO NA TABELA FILME
--
--------------------------------------------------------------------------------
@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)
RAISERROR('ALERTA: A INCLUSÃO NA TABELA [FILME] NÃO PODE SER FEITA - O REGISTRO JÁ EXISTE NO BANCO', 16, 1)
ELSE
BEGIN
INSERT INTO FILME (
NOM_FILME,
NOM_ORIGINAL_FILME,
ANO_FILME
)
VALUES (
@NOM_FILME,
@NOM_ORIGINAL_FILME,
@ANO_FILME
)
IF @@ERROR<>0
RAISERROR('ERRO: A INCLUSÃO NA TABELA [FILME] NÃO PODE SER FEITA - ERRO NA PROCEDURE [_SP_FILME_INCLUIR]', 16, 1)
END
GO
--------------------------------------------------------------------------------
-- FIM DA PROCEDURE ------------------------------------------------------------
--------------------------------------------------------------------------------
Aguarde as próximas três matérias, onde continuarei a mostrar a criação automática das procedures. Até mais.
Veja a primeira etapa deste artigo em:
//www.devmedia.com.br/articles/visualizacomponente2.asp?comp=2207