Assign execute command to a variable

Question

Assign execute command to a variable

Navigation

#1 by (2 votes)

2

I need the return of the execute command to be assigned to variable @PAGO because I will insert it into a temporary table only what returns is the script, not the value.

DECLARE @COMANDO_COMANDO_PAGO VARCHAR(2000)
   SET @SITUACAO = 'PAGO'
    SET @COMANDO_COMANDO_PAGO = 'select  count(f.matricula)
    from matricula m
        inner join aluno a
    on a.matricula = m.matricula
        inner join financeiro f
    on m.matricula = f.matricula
        and m.ano = f.ano and m.semestre = f.semestre and m.periodo = f.periodo
    where m.ano = ' + CONVERT(VARCHAR,@ANO) + ' and m.semestre = ' + CONVERT(VARCHAR,@SEMESTRE) + '

EXEC(@COMANDO_COMANDO_PAGO)
SET @PAGO = @COMANDO_COMANDO_PAGO

temporary table

INSERT INTO #temp_finc VALUES (@PERIODO,@PAGO,@EM_ABERTA)

sql sql-server procedure cursor

asked by anonymous 12.10.2014 / 19:59

1 answer

Personality is not avaliable Obtain a string with a regular expression in JS

score 2 · Accepted Answer

To use this, you need to have your dynamic query support a return value. Something like this:

SET @COMANDO_COMANDO_PAGO = 'select @Pago = count(f.matricula)
from matricula m
    inner join aluno a
on a.matricula = m.matricula
    inner join financeiro f
on m.matricula = f.matricula
    and m.ano = f.ano and m.semestre = f.semestre and m.periodo = f.periodo
where m.ano = @ANO and m.semestre = @SEMESTRE'

And then run with sp_executesql as follows:

SET @ParamDefinition = N'@ANO varchar, @SEMESTRE varchar, @Pago int OUTPUT';
EXEC sp_executesql @COMANDO_COMANDO_PAGO, @ParamDefinition, @ANO=CONVERT(VARCHAR,@ANO), @SEMESTRE=CONVERT(VARCHAR,@SEMESTRE), @Pago=@Pago OUTPUT;