How to execute a procedure in a select?

Question

How to execute a procedure in a select?

Navigation

#1 by (1 votes)

1

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

Does anyone have a solution?

sql sql-server

asked by anonymous 31.05.2015 / 21:04

1 answer

Error message generating report Save data and insert into CheckBox

score 1 · Answer 1

Stored procedures are not allowed in conjunction with SELECT, WHERE, or HAVING statements.

I can suggest an alternative. You create a temporary table to save the output of the stored procedure and then you use this 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 procedure that returns a table. Unlike stored procedures, the functions can be used in conjunction with the SELECT statement.

With a function the syntax would look 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