View joining with stored Procedure results

Question

I have a existing view in SQL server used by the application. I need to join with table returned from the stored procedure. The stored procedure does lot of things like inserting to multiple #temp tables before returning the result.

I tried to convert the stored procedure to Table valued function. But inserting to temp table inside the TVF causes compilation error.

Is there any other way I can achieve this.

Thanks

Well, you could *try* creating a #temp table first, and then `INSERT #t EXEC dbo.procedure;` but then you may run into nested insert/exec issues. Why don't you try writing a version of the procedure in a TVF *without* using #temp tables> — Aaron Bertrand, Aug 07 '13 at 12:44
Sorry, no direct method. Only via e.g. intermediate structure like table variable or temp table — OzrenTkalcecKrznaric, Aug 07 '13 at 12:44
@AaronBertrand StoredProc stores the intermediate results in multiple #temp table based on logic. How can this be done without using #tempTable in TVF. Is there any alternative — Kevin Pieterson, Aug 07 '13 at 12:54
Sure, don't use intermediate logic. Can't tell you how to do that without seeing the logic. — Aaron Bertrand, Aug 07 '13 at 12:56

score 3 · Answer 1 · answered Aug 07 '13 at 12:43

3

You can insert the results from the Stored Procedure into a temp table, and then join that onto the view.

Have a look at the below example

SQL Fiddle DEMO

CREATE TABLE TADA(
  ID INT
);
INSERT INTO TADA VALUES (1),(2);

CREATE VIEW vw_TADA
AS
SELECT *
FROM TADA
WHERE ID <= 1;

CREATE PROCEDURE sp_TADA
AS
SELECT *
FROM TADA
WHERE ID > 1;

CREATE TABLE #TADA(
  ID INT
)
INSERT INTO #TADA EXEC sp_TADA

SELECT *
FROM vw_TADA
UNION ALL
SELECT *
FROM #TADA

answered Aug 07 '13 at 12:43

Adriaan Stander

162,879
31
289
284

But if the procedure also does insert/exec that may not work. – Aaron Bertrand Aug 07 '13 at 12:44
Yes, Stored procedure does insert to temp table and has exec statement. – Kevin Pieterson Aug 07 '13 at 12:46

score 0 · Answer 2 · edited May 23 '17 at 12:00

Another trick is to use OPENQUERY. From my answer here:

It requires the use of OPENQUERY and a loopback linked server with the 'DATA ACCESS' property set to true. You can check sys.servers to see if you already have a valid server, but let's just create one manually called loopback:

EXEC master..sp_addlinkedserver 
    @server = 'loopback',  
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = @@SERVERNAME;

EXEC master..sp_serveroption 
    @server = 'loopback', 
    @optname = 'DATA ACCESS',
    @optvalue = 'TRUE';

Now that you can query this as a linked server, you can use the result of any query (including a stored procedure call) as a regular SELECT. So you can do this (note that the database prefix is important, otherwise you will get error 11529 and 2812):

SELECT * FROM OPENQUERY(loopback, 'EXEC db.dbo.procedure;') AS x;

Now you can join to your view.

But I honestly feel it will be better if you re-write the procedure as a TVF and stop using #temp tables in the logic. The above might work on your current instance but it won't work in SQL Server 2012 (metadata can't be determined because of the #temp table in the procedure) and it won't work if you have certain database- or server-level DDL triggers either (for the same type of reason).

Also see http://www.sommarskog.se/share_data.html#OPENQUERY for other info and limitations.

score 0 · Answer 3 · answered Aug 07 '13 at 13:08

Just to add another method to process such a data, you could use shared temporary table or output xml parameter to get data from the stored procedure. Actually it requires to modify your procedure, so it could be not an option. General advice is try to rewrite your procedure into table function (you could change temporary tables into table variables)

xml parameter

CREATE PROCEDURE sp_Process2
(
  @Data xml = null output,
  @Fill_Data bit = 0
)
AS
begin
   create table #Test1 (ID int, Name nvarchar(128), Col3 nvarchar(128))

   -- do some work
   insert into #Test1
   select 1, 'From Procedure', 'Unused'

   if @Fill_Data = 1 -- put data into xml parameter
   begin
      select @Data = 
      (
          select *
          from #Test1
          for xml raw('Data')
      )
   end
   else -- just return recordset
   begin
      select * from #Test1
   end
end;

shared table (dynamic SQL is completely optional, just to pass table name and columns into procedure)

CREATE PROCEDURE sp_Process
(
  @Table_Name nvarchar(128) = null,
  @Columns nvarchar(max) = null
)
AS
begin
   declare @stmt nvarchar(max)
   create table #Test1 (ID int, Name nvarchar(128), Col3 nvarchar(128))

   -- do some work
   insert into #Test1
   select 1, 'From Procedure', 'Unused'

   if @Table_Name is not null -- put data into temporary table
   begin
      select @stmt = 'insert into ' + quotename(@Table_Name) + ' select ' + @Columns + ' from #Test1'
      exec sp_executesql @stmt = @stmt
   end
   else -- just return recordset
   begin
      select * from #Test1
   end
end;

see sql fiddle with examples

View joining with stored Procedure results

3 Answers3

SQL Fiddle DEMO

Linked