set variable Query dynamic

Question

set variable Query dynamic

Navigation

#1 by (1 votes)

1

Set variable within a dynamic query and for each database obtain the user result with the role 'db_owner' and represent them in '1' if you find results and '0' if you do not find.

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 [' +@3_13 +'] = 1
    END
    ELSE
    BEGIN
        SET [' +@3_13 +'] = 0
    END     ');

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

sql sql-server tsql

asked by Cristian Zauco 04.07.2018 в 16:55

source

1 answer

Consultation of two Models in Django How to make a list of images using picasso?

score 1 · Accepted Answer

You can not do it the way you try. First of all, when you write:

EXEC (N' ...
         ...
SET [' +@3_13 +'] = 0
         ...
);

What I understand you are trying to do is that the dynamic query ends by setting the value of @3_13 , but you have several problems:

In the first place, you have to understand, that as you have written it, what you actually do is concatenate the value of @3_13 to the chain, so if we forget that @3_13 is a whole and can not concatenate to a string as well and also that the value of @3_13 is initially NULL, if @3_13 worth for example the string Ok , what you do is ultimately generate a statement like this SET [OK] = 0 , which obviously does not make sense.
The other problem is that a dynamic query does not have access to the variables external to that query to modify them, so anyway it is not possible to do something like this: SET @3_13 = 0 .

Solution:

There are some possibilities, the one I think is the right one is to use sp_executesql with output variables. Let's see in a conceptual way how you could solve it:

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

Detail:

Now the query internally handles a new variable @Retorno , could be the same name, but to make it clearer the explanation I called it another name.
@Retorno is going to be a variable of the query, to be able to access from outside of them, we will have to use sp_executesql , and you have to declare it in the call as OUTPUT ( @Retorno INT OUTPUT ) that is, it will be a Output variable.
Finally, we indicate @Retorno= @3_13 OUTPUT , which allows to establish is that the internal output value in @Retorno will be the input, now yes, to our external variable @3_13