Take a variable value from Sql Server

Question

I have this Sql block:

SET NOCOUNT ON;

    Declare 
    @QueryString as NVarchar(4000), @Datazero as varchar(20),  @Datamn as varchar(20),
    @CdUsuario as integer,
    @SitProcesso as int
   ,@DsUsuarioRede varchar(20)
   ,@Processo as int
   ,@Grupo as int
   ,@Cota as smallint
   ,@DtCriacao as varchar(20)

SET @CdUsuario = (SELECT CdUsuario FROM Seguranca.TBUsuario WHERE DsUsuarioRede = @DsUsuarioRede)
SET @Processo = 46

    -- Lista os processos baseado em filtros
    Set @QueryString = N'
SELECT 
            CP.CdProcesso, 
            CP.DtCriacao, 
            (select TA.DtInicio 
               from Processo.TBAnalise TA 
              where TA.CdProcesso = CP.CdProcesso 
                and TA.CdAnalise = (select max(TBA.CdAnalise) 
                                      from Processo.TBAnalise TBA 
                                     where TBA.CdProcesso = TA.CdProcesso)) DtInicio,
            (select TA.DtFim 
               from Processo.TBAnalise TA 
              where TA.CdProcesso = CP.CdProcesso 
                and TA.CdAnalise = (select max(TBA.CdAnalise) 
                                      from Processo.TBAnalise TBA 
                                     where TBA.CdProcesso = TA.CdProcesso)) DtFim,
            CP.CdGrupo, 
            CP.CdCota, 
            ISNULL(AWF.NmWorkFlowItem, ''Processo Iniciado - PV'') NmWorkFlowItem, 
            AWF.CdWorkFlowItem, 
            DTS.NmTipoStatus, 
            WFR.CdUsuario, 
            CASE WHEN (select max(TBA.CdAnalise) 
                         from Processo.TBAnalise TBA 
                        where TBA.CdProcesso = CP.CdProcesso) IS NULL AND AWF.CdTipoUsuario != 3 THEN ''AGUARDANDO ANÁLISE'' 
                 ELSE (CASE WHEN  (select max(TBA.CdAnalise) 
                                     from Processo.TBAnalise TBA 
                                    where TBA.CdProcesso = CP.CdProcesso) IS NULL AND AWF.CdTipoUsuario = 3 THEN ''PONTO DE VENDA'' 
                 ELSE ISNULL((select TBU.NmUsuario 
                                from Processo.TBAnalise TA, 
                                     Seguranca.TBUsuario TBU 
                               where TA.CdProcesso = CP.CdProcesso   
                                 and TA.CdAnalise = (select max(TBA.CdAnalise) 
                                                       from Processo.TBAnalise TBA 
                                                      where TBA.CdProcesso = TA.CdProcesso) 
                                 and TA.CdUsuario = TBU.CdUsuario), TBUP.NmUsuario)END)END NomeUsuario,
            AWF.CdTipoUsuario,
            (select max(TBA.CdAnalise) from Processo.TBAnalise TBA where  TBA.CdProcesso = CP.CdProcesso) CdAnalise,
            (select TA.CdUsuario from Processo.TBAnalise TA where TA.CdProcesso = CP.CdProcesso and TA.CdAnalise = (select max(TBA.CdAnalise) from Processo.TBAnalise TBA where  TBA.CdProcesso = TA.CdProcesso)) CdUsuarioAn,
            CASE 
                WHEN isnull((select TA.IcPriorizado from Processo.TBAnalise TA where TA.CdProcesso = CP.CdProcesso and TA.CdAnalise = (select max(TBA.CdAnalise) from Processo.TBAnalise TBA where  TBA.CdProcesso = TA.CdProcesso)),0) = 0 THEN
                    null
                ELSE
                    ''checked''
                END IcPriorizado
        FROM 
            Processo.TBProcesso AS CP 
                INNER JOIN Dominios.TBTipoProcesso AS TP 
                    ON CP.CdTipoProcesso = TP.CdTipoProcesso 
                INNER JOIN Processo.TBWorkFlowRealizacao AS WFR 
                    ON CP.CdProcesso = WFR.CdProcesso 
                    LEFT JOIN Seguranca.TBUsuario TBUP
                        ON WFR.CdUsuario = TBUP.CdUsuario
                INNER JOIN Dominios.TBTipoStatus AS DTS 
                    ON CP.CdTipoStatus = DTS.CdTipoStatus 
                LEFT OUTER JOIN Processo.TBWorkFlowAtividade AS AWF 
                    ON WFR.CdWorkFlowItem = AWF.CdWorkFlowItem                  
        WHERE 
            WFR.DtCriacao = 
            (
                SELECT MAX(DtCriacao) 
                FROM Processo.TBWorkFlowRealizacao 
                WHERE CdProcesso = CP.CdProcesso
            )
        AND Exists((select 1 
            from ScoCons..Cota as cota, 
                 Seguranca.TBGrupoUsuario as gpusu
            where CP.CdGrupo = cota.Grupo
            and CP.CdCotaMPS = cota.Cod
            and gpusu.CdGrupo = cota.Conces
            and gpusu.CdUsuario = ' + Cast(@CdUsuario as varchar) + ')) ' 

    IF @Processo IS NOT NULL
        BEGIN
            SET @QueryString = @QueryString + ' AND CP.CdProcesso = ' + Cast(@Processo as varchar)
        END

    IF @Grupo IS NOT NULL
        BEGIN
            SET @QueryString = @QueryString + ' AND CP.CdGrupo = ' + Cast(@Grupo as varchar)

        END

    IF @Cota IS NOT NULL
        BEGIN
            SET @QueryString = @QueryString + ' AND CP.CdCota = ' + Cast(@Cota as varchar)
        END

    IF @SitProcesso IS NOT NULL
        BEGIN
            SET @QueryString = @QueryString + ' AND CP.CdTipoStatus = ' + Cast(@SitProcesso as varchar)
        END

--  IF @CdUsuario IS NOT NULL 
--      BEGIN
--          SET @QueryString = @QueryString + ' AND WFR.CdUsuario = ' + Cast(@CdUsuario as varchar)
--      END

    IF @DtCriacao IS NOT NULL
        BEGIN
            set @Datazero   = LEFT(@DtCriacao, 4) + '/' + SUBSTRING(@DtCriacao, 6, 2) + '/' + SUBSTRING(@DtCriacao, 9, 2) + ' 00:00:00' 
            set @Datamn     = LEFT(@DtCriacao, 4) + '/' + SUBSTRING(@DtCriacao, 6, 2) + '/' + SUBSTRING(@DtCriacao, 9, 2) + ' 23:59:59'
            SET @QueryString = @QueryString + ' AND CP.DtCriacao >= ''' + @Datazero + ''' AND CP.DtCriacao <= ''' + @Datamn + ''''  
        END

        SET @QueryString = @QueryString + ' ORDER BY CP.CdProcesso'

        --EXEC sp_executesql @QueryString 
        print @QueryString

How do I get the value of @Querystring at the time of execution? I gave one print as you can see, but there’s nothing on the way out.

Answer 1

Hi, try to select your variable:

select  @QueryString  

instead of print, :)

Answer 2

The query being saved in the @Querystring variable exceeds the size. You need to declare the variable to varchar(5000).

Declare 
    @QueryString as varchar(5000)

Answer 3

So I understand you want the result of your dynamically generated Query.

If so, try instead of print.

exec(@QueryString)