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

Related Posts:

Remover 0 de resultado de fecha – sql sql-server
Pregunta: Realizo este comando para traerme la fecha en un formato YYYY/MM/DD: DECLARE @SomeExampleDate DATETIME; SELECT @SomeExampleDate = '2016/08/20'; SELECT STUFF(REPLACE('/'+CONVERT(CHAR(10),@SomeExampleDate,102), '.', '/'),1,1,'') Pero quiero que la fecha que ...
Como Insertar Fecha en SQLite – sql sqlite sqlite3
Pregunta: Tengo una consulta: Tengo una tabla en SQLite con esta estructura CREATE TABLE ( VARCHAR(50) NULL, TIMESTAMP NULL, TIMESTAMP NULL ...
¿Cómo obtener todos los registros duplicados en postgresql? – sql postgresql
Pregunta: Como podría obtener todos los registros duplicados en base a una columna, por ejemplo tengo la tabla codigo: ╔════╦══════════╦══════════╗ ║ id ║ codigo_1 ║ codigo_2 ║ ╠════╬══════════╬══════════╣ ║ ...
Problema con UTF_8 – php sql sql-server
Pregunta: Buen día, mi problema es el siguiente, Php me lanza el siguiente Warning Warning: utf8_decode() expects parameter 1 to be string, object given ...
¿Cómo utilizar `count` para contabilizar la cantidad de registros para distintos valores de un campo en mysql? – php mysql sql
Pregunta: Resulta que tengo un campo llamado ESTADO en mi tabla. En este campo hay varias estados de pedido que son: LOGÍSTICA, PACKING, PICKING, CARTERA, ...
Insertar en una tabla registros basados en el resultado de una consulta – mysql sql
Pregunta: Estoy intentando que insertar una fila nueva con unos datos por cada valor que de un SELECT. Os dejo la última query que se me ...
Seleccion desde varias tablas – asp.net sql-server entity-framework
Pregunta: Tengo esta consulta que funciona bien var rest = (from de in se.DeliveryConfiguration ...
Problema con los Index Primary Key identity dan saltos enormes en tablas. – sql sql-server
Pregunta: En algunas ocasiones la base de datos me han dado salto digamos que de de ir en 200,000 hasta 250,000 o incluso mayores. Alguna ...
¿Como usar una sentencia Case en Mysql? – mysql sql
Pregunta: Tengo dos tablas una es la de detalle de producto y la otra es la de producto, el problema radica en que yo quiero ...
Inconvenientes con Store Procedure – sql sql-server
Pregunta: Estoy realizando un SP para realizar una búsqueda específica con parametros, pero me trae el siguiente error. ¿Cómo solucionarlo? Msg 137, Level 15, ...
buscar las 3 fechas más antiguas en SQL – mysql sql
Pregunta: Tengo un problema con una consulta. La tabla tiene los siguientes campos: Tabla1 --------+-----------+---------+------------------ Id | Inicio | Fin ...
SQL Error 18456 al conectarme al servidor – sql sql-server
Pregunta: Estoy teniendo problemas para conectarme con "SQL Sever Authentication". Cuando instalé el programa en la parte de Database Engine Configuration seleccioné modo de autenticación ...
Cómo importar datos de un Excel a DataGrid pero iniciarlo en la segunda fila? – c# sql-server postgresql
Pregunta: Disculpen una duda, como cargar de excel un formato iniciando en la segunda fila? tengo este código solamente que el excel esta justificado en ...
Clausula Count(*) dentro de un select – sql sql-server
Pregunta: Tengo la siguiente consulta SQL. Lo que no se como hacer es que el count(*), me devuelva una columna mas, con el total de registros. La ...
¿Como puedo tomar un dato de una DB y sumarlo a otro dato de tipo DateTime en C#? – c# sql-server windows-forms
Pregunta: Mi duda es esta: Estoy usando Visual Studio 2015, y estoy programando en C# para Windows Forms, el caso que es tengo una variable ...

Add a Comment

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