setear variable Query dinamico – sql sql-server tsql

Pregunta:


Setear variable dentro de un query dinamico y por cada base de datos obtener el resultado de usuario con el role 'db_owner' y representarlos en '1' si encuentra resultados y '0' si no encuentra.

BEGIN
    DECLARE @baseDeDatos VARCHAR(50)
    DECLARE @inicio INT
    DECLARE @contador INT
    DECLARE @3_13 INT
    SET @inicio = (
    SELECT 
        MIN(database_id)
        FROM 
        sys.databases
        WHERE name IS NOT NULL 
        and name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB'))

    WHILE (@inicio <= (SELECT MAX(database_id) FROM sys.databases))
    BEGIN
            SELECT 
                @baseDeDatos = name  
            FROM 
            sys.databases
            WHERE database_id = @inicio
            PRINT @baseDeDatos

            BEGIN
                EXEC (N'
                USE [' + @baseDeDatos + '];

IF (SELECT 
            COUNT(*)
        FROM
            sys.database_principals dbp
            INNER JOIN
            sys.database_role_members drm
            ON dbp.principal_id = drm.member_principal_id
            INNER JOIN
            sys.database_principals dbpt
            ON  drm.role_principal_id = dbpt.principal_id
        WHERE dbpt.name = ''db_owner'' and dbp.name <> ''dbo'') > 0
    BEGIN
        SET [' [email protected]_13 +'] = 1
    END
    ELSE
    BEGIN
        SET [' [email protected]_13 +'] = 0
    END     ');

            END
        SET @inicio = @inicio  + 1;
    END
END

Preguntado por: Cristian Zauco

Respuesta Patricio Moracho:

No puedes hacerlo de la forma en que lo intentas. Primero que nada, cuando escribes:

EXEC (N' ...
         ...
SET [' [email protected]_13 +'] = 0
         ...
);

Lo que entiendo que intentas es que la consulta dinámica termine seteando el valor de @3_13, pero tienes varios problemas:

  • En primer lugar, tienes que entender, que tal como lo has escrito, lo que en realidad haces es concatenar el valor de @3_13 a la cadena, por lo que si nos olvidamos que @3_13 es un entero y no se puede concatenar a una cadena así como así y además que el valor de @3_13 es inicialmente NULL, si @3_13 valiera por ejemplo la cadena Ok, lo que haces en definitiva es generar una sentencia como esta SET [OK] = 0, que obviamente no tiene sentido.

  • El otro problema es que una consulta dinámica no tienes acceso a las variables externas a dicha consulta como para modificarlas, por lo que de todas formas no es posible hacer algo como esto: SET @3_13 = 0.

Solución:

Hay algunas posibilidades, la que me parece la adecuada es usar sp_executesql con variables de salida. Veamos de manera conceptual como podrías resolverlo:

DECLARE @SQL         NVARCHAR(MAX)
DECLARE @baseDeDatos VARCHAR(50)
DECLARE @3_13        INT

SELECT  @baseDeDatos    = 'master'

SELECT @SQL ='
USE [' + @baseDeDatos + '];

IF (SELECT 
            COUNT(*)
        FROM
            sys.database_principals dbp
            INNER JOIN
            sys.database_role_members drm
            ON dbp.principal_id = drm.member_principal_id
            INNER JOIN
            sys.database_principals dbpt
            ON  drm.role_principal_id = dbpt.principal_id
        WHERE dbpt.name = ''db_owner'' and dbp.name <> ''dbo'') > 0
BEGIN
    SET @Retorno = 1
END ELSE BEGIN
    SET @Retorno = 0
END
'

EXECUTE sp_executesql @SQL,
        N'@Retorno INT OUTPUT',
        @Retorno    = @3_13 OUTPUT


SELECT @3_13

Detalle:

  • Ahora la consulta internamente maneja una nueva variable @Retorno, podría ser el mismo nombre, pero para que quede más clara la explicación la llamé de otro nombre.
  • @Retorno va a ser una variable de la consulta, para poder acceder desde afuera de la mismas, deberemos usar sp_executesql, y hay que declararla en la llamada como OUTPUT (@Retorno INT OUTPUT) es decir, será una variable de salida.
  • Por último, indicamos @Retorno= @3_13 OUTPUT, lo que permite establecer es que el valor interno de salida en @Retorno será la entrada, ahora sí, a nuestra variable externa @3_13

Fuente

Add a Comment

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *