Retrieve data from SP inside of From

Question

I would like to use [sp_Test] to retrieve the data from the syntax code "FROM" but it does not work. Do you know how to solve it?

Thanks!

enter image description here

create table datatable (id int,
                        name varchar(100),
                        email varchar(10),
                        phone varchar(10),
                        cellphone varchar(10),
                        none varchar(10)                        
                       );

insert into datatable values
  (1, 'productname', 'A', 'A', 'A', 'A'), 
  (2, 'cost', '20', 'A', 'A', 'A'),
  (3, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (4, 'productname', 'A', 'A', 'A', 'A'), 
  (5, 'cost', '20', 'A', 'A', 'A'),
  (6, 'active', 'Y', 'A', 'A', 'A');


insert into datatable values
  (7, 'productname', 'A', 'A', 'A', 'A'), 
  (8, 'cost', '20', 'A', 'A', 'A'),
  (9, 'active', 'Y', 'A', 'A', 'A');


CREATE PROCEDURE [sp_Test]
as
begin
    set nocount on

    SELECT a.name, a.email, a.phone
    FROM datatable a 

end

score 0 · Accepted Answer · edited May 23 '17 at 10:27

First you should never name your stored procedure with sp_ prefix. It is very bad habit and can collide with system stored procedures.

Second you cannot simply use SELECT FROM EXEC, but you can create temporary table/variable to hold result and query it like:

DECLARE @t AS TABLE ( name varchar(100),
                      email varchar(10),
                      phone varchar(10));

INSERT INTO @t
EXEC [dbo].[sp_Test];

SELECT *
FROM @t;

SqlFiddleDemo

There is also workaround using loopback linked server and OPENQUERY:

SELECT * 
FROM OPENQUERY(LOCALSERVER, 'EXEC [sp_Test]');

I strongly recommend to read:

Retrieve data from SP inside of From

1 Answers1