Assign execute command to a variable

Asked

Viewed 2,608 times

2

I need the return of the execute command to be assigned to the @PAGO variable because I will insert it into a temporary table only what returns is the script and 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

1 answer

2


To use it like this, you need to make your dynamic query admit 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 the 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;

Browser other questions tagged sql sql-server procedure cursor

You are not signed in. Login or sign up in order to post.