Problema con Cursor

[Juan228]

Hola a todos, les comento:

Yo le envio a mi SP El nombre de una Base de Datos (Por ejemplo AdventureWorks) y el nombre de un schema (Por Ejemplo HumanResources) y en base a eso tengo que crear una nueva tabla "Resultado" en donde figuren todos los SP y Tablas que componen ese Schema.

Entre los datos que me piden esta que si es una TABLA indique si posee PK y FK, por lo cual a traves del INFORMATION_SCHEMA obtengo por una parte las tablas y por el otro las constraint, por lo cual tendria que hacer un join.


Los problemas en este caso son 2:

1. Yo estoy parado sobre otra base de datos y estas consultas las hago contra AdventureWorks
2. Tengo que recorrer SI O SI las filas por que cada fila puede tener diferentes resultados

Asi que lo que se me ocurrio, aunque poco practico, es usar dentro de mi SP un cursor, pero me da el error:

Could not complete cursor operation because the set options have changed since the cursor was declared.


Les dejo el codigo completo a ver si me pueden ayudar por qeu me estoy volviendo loco!!

Código SQL:

-- ======================================================= --
-- ===========> CREACION DE STORAGE PROSEDURE <=========== --
-- ======================================================= --
 
CREATE PROCEDURE sp_grabar @SCHEMA VARCHAR(50), @DB VARCHAR(50)
AS
BEGIN
 
DECLARE @ERRORMSJ VARCHAR(MAX)      -- Variable de Mensaje de Error
DECLARE @PK VARCHAR(2)              -- Variable de SI o NO
DECLARE @PK_Compuesta VARCHAR(2)    -- Variable de SI o NO
DECLARE @FK VARCHAR(2)              -- Variable de SI o NO
DECLARE @FK_Compuesta VARCHAR(2)    -- Variable de SI o NO
DECLARE @CANTIDAD INT               -- Variable Acumuladora
DECLARE @query NVARCHAR(MAX)        -- Variable para consultas dinamicas
DECLARE @INICIO INT                 -- Variable para recorrer el while
DECLARE @cambiarDB VARCHAR(MAX)     -- Declaro una variable para poder cambiar de DB en el codigo
    SET @cambiarDB='USE '+@DB+ '; '
 
BEGIN TRY
    ---------------------------------------------------------------------------
    -- ================> Verifico si la Base de Datos Existe <============== --
    ---------------------------------------------------------------------------
    /*
    SET @ERRORMSJ = 'ERROR: La Base de Datos no existe'
    IF DB_ID(@DB) IS NULL RAISERROR(@ERRORMSJ,16,1)
    */
    ---------------------------------------------------------------------------
    -- ======================> Verifico PK y PK COMPUESTA <==================== --
    ---------------------------------------------------------------------------
 
    ---------------------------------------------------------------------------
    -- ===========> Hago los Insert para las Tablas del Esquema <=========== --
    ---------------------------------------------------------------------------
    -- Declaro el cursor
    SET @query=@cambiarDB+'DECLARE cursorTablas CURSOR LOCAL FOR(SELECT * FROM INFORMATION_SCHEMA.TABLES)'
    EXECUTE sp_executesql @query
 
    -- Declaro variables para manejar el cursor
    DECLARE @TABLE_CATALOG NVARCHAR(MAX)
    DECLARE @TABLE_SCHEMA NVARCHAR(MAX)
    DECLARE @TABLE_NAME NVARCHAR(MAX)
    DECLARE @TABLE_TYPE NVARCHAR(MAX)
 
    -- Hago la apertura del Cursor
    OPEN cursorTablas
 
    -- Recorro el Cursor
    FETCH NEXT FROM cursorTablas INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @TABLE_TYPE
        WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT INTO Resultado(esquema, base, objeto, tipoObjeto) VALUES (@TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @TABLE_TYPE)
 
            FETCH NEXT FROM cursorTablas INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @TABLE_TYPE
        END
    
    close cursorTablas
    Deallocate cursorTablas
 
    /*
    SET @query='INSERT INTO Resultado(esquema, base, objeto, tipoObjeto) SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE  
                from ' + @DB + '.INFORMATION_SCHEMA.TABLES where TABLE_TYPE=' +'''BASE TABLE''' + ' AND TABLE_SCHEMA=' + '''' + @SCHEMA + ''''
 
    EXEC sp_executesql @query
    */
 
    
    ---------------------------------------------------------------------------
    -- =============> Hago los Insert para los SP del Esquema <============= --
    ---------------------------------------------------------------------------
    SET @query='INSERT INTO Resultado(esquema, base, objeto, tipoObjeto, parametros) SELECT DISTINCT rou.SPECIFIC_CATALOG as Base, rou.SPECIFIC_SCHEMA as Esquema, rou.ROUTINE_NAME as Nombre, rou.ROUTINE_TYPE as Tipo, 
                CASE WHEN par.PARAMETER_MODE IN (''IN'',''INOUT'') THEN ''SI'' ELSE ''NO'' END as Parametros
                FROM ' + @DB + '.INFORMATION_SCHEMA.ROUTINES as rou
                    INNER JOIN ' + @DB + '.INFORMATION_SCHEMA.PARAMETERS as par
                    ON rou.ROUTINE_NAME = par.SPECIFIC_NAME
                WHERE ROUTINE_TYPE = ''PROCEDURE'' AND rou.SPECIFIC_SCHEMA = ' + '''' + @SCHEMA + ''''
 
    EXEC sp_executesql @query
 
END TRY
 
BEGIN CATCH -- Inserto el error en la tabla de Logs de Errores
 
    INSERT INTO LOGERRORES (descripcion, error, fila, errorLinea, errorMensaje, errorProcedure, errorNumero, errorSeveridad, errorEstado, fececha, idUsuario) VALUES
            (@ERRORMSJ, @@ERROR, @@ROWCOUNT, ERROR_LINE(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_NUMBER(), ERROR_SEVERITY(),ERROR_STATE(), GETDATE(),SYSTEM_USER)
 
    RAISERROR (@ERRORMSJ, 16 , 1)
 
END CATCH
 
END
GO
 
 
-- ======================================================= --
-- ===================> CONSULTAS <======================= --
-- ======================================================= --
/* */
EXEC sp_grabar HumanResources, AdventureWorks
 
---------------------------------------------------------------------------
-- =======================> Listado de Select <========================= --
---------------------------------------------------------------------------
SELECT * FROM Auditoria
SELECT * FROM LOGERRORES
 
SELECT  esquema AS ESQUEMA,
        base AS BASE,
        objeto AS OBJETO,
        ISNULL(tipoObjeto,' ') AS 'TIPO DE OBJETO',
        ISNULL(parametros,' ') AS PARAMETROS,
        ISNULL(pk,' ') AS PK,
        ISNULL(pkCompuesta,' ') AS 'PK COMPUESTA',
        ISNULL(fk,' ') AS FK,
        ISNULL(fkCompuesta,' ') AS 'FK COMPUESTA'
FROM Resultado
 

[iislas]

Hola

Verifique el resultado de estas dos consultas, ¿podria tomar los datos de este resultado?

Código SQL:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
SELECT * FROM INFORMATION_SCHEMA.TABLES
 

[Juan228]

Iniciado por iislas

Hola

Verifique el resultado de estas dos consultas, ¿podria tomar los datos de este resultado?

Código SQL:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
SELECT * FROM INFORMATION_SCHEMA.TABLES
 

Hola! Lo solucioneponiendo la palabra LOCAL! al momento de crear el cursor

"CURSOR LOCAL FOR...."

[Libras]

Y para que el cursor? porque no haces el insert directamente? no veo que tengas ninguna validacion ni que revises linea por linea los resultados?


podrias hacer algo como esto:

Código SQL:

DECLARE @query Nvarchar(MAX)
DECLARE @db Nvarchar(10)
 
SET @query='insert into resultado
select * from ' + @db + 'information_schema.tables'
sp_executesql @query
 

No veo la necesidad de usar un cursor.....

[iislas]

Bueno Mr. Libras, hay gente que para matar una cucharacha utiliza una AK-47, asi de simple....cada quien mata la cucaracha como mas le guste.

[Libras]

Iniciado por iislas

Bueno Mr. Libras, hay gente que para matar una cucharacha utiliza una AK-47, asi de simple....cada quien mata la cucaracha como mas le guste.

A no bueno en eso usted tiene razon.....

P.D: Me tome la libertad de robarle la frase