How to run a trial in a select?

Question

What I tried was the following, and my Procedure returns only a select:

    SELECT 
        *
    FROM
        t_usuarios
    WHERE
        nome like '%jeniffer%'
    AND
        cd_usuario IN (exec usp_lista_emprestimo_em_atraso)
    ORDER BY
        nome

Someone has a solution?

Answer 1

The use of stored procedures is not permitted in conjunction with the SELECT, WHERE or HAVING instructions.

I can suggest an alternative. Create a temporary table to store the output of the stored Procedure and then use that table in your query.

if object_id('tempdb..#emprestimoAtraso') is not null
   drop table #emprestimosAtraso

create table #emprestimosAtraso(

   cd_usuario       int -- alterar de acordo com o output da stored procedure

)

insert into #emprestimosAtraso(cd_usuario)
exec usp_lista_emprestimo_em_atraso

select *
from  t_usuarios tbl_u
inner join #emprestimosAtraso tbl_e
   on tbl_e.cd_usuario = tbl_u.cd_usuario
where nome like '%jeniffer%'
order by nome

Another alternative is to create a function equivalent to your stored plan that returns a table. Unlike stored procedures, functions can be used in conjunction with the SELECT instruction.

With a function the syntax would be, for example, like this:

select *
from  t_usuarios tbl_u
inner join udf_emprestimosAtraso() udf_e
   on udf_e.cd_usuario = tbl_u.cd_usuario
where nome like '%jeniffer%'
order by nome