Can you do a select on the results of a stored procedure in T-SQL

Question

select * from (EXEC sp_SomeStoredProc)

If you can't do this then what is stopping it from being added to the SQL standard or T-SQL?

Answer 1

You can't do this, however you can do it as an insert. e.g.

insert mytable
exec myStoredProcedure

Also, never name your stored procedures sp_xxxx. This is because SQL will always search in the system stored procedure area due to the sp_ before looking in the user stored procedures, leading to a small loss in performance that could add it to be fairly significant on a process that is run frequently.

Answer 2

What if the stored proc returns no rows? Multiple result sets? Changes? The potential usages of a stored proc are many and varied.

When you have SELECT * FROM TableOrView, there is a direct binding and easily checked syntax and structure.

More correctly, in the relational sense, a stored proc is not a relation/table so you can't select from it.

User defined functions achieve what you want but allow the code to conform to some relation/table concept.

Answer 3

You can't do it, but you could consider a function in sqlserver2005. Here's an example function that creates a table from a comma separated list

Create Function [dbo].[CsvToInt] ( @Array varchar(1000)) 
returns @IntTable table 
    (IntValue int)
AS
begin

    declare @separator char(1)
    set @separator = ','

    declare @separator_position int 
    declare @array_value varchar(1000) 

    set @array = @array + ','

    while patindex('%,%' , @array) <> 0 
    begin

      select @separator_position =  patindex('%,%' , @array)
      select @array_value = left(@array, @separator_position - 1)

     Insert @IntTable
     Values (Cast(@array_value as int))

      select @array = stuff(@array, 1, @separator_position, '')
    end

    return
end

And then simple select from the function...

Select * FROM dbo.CsvToInt('1,2,3,5')

And you'll get a table value.

Answer 4

It's possible, but certainly not the right way to go:

USE test
GO
CREATE procedure dbo.select1 AS
SELECT 1 
GO
EXEC sp_addlinkedserver @server='OQtest', @datasrc='localhost', @provider='SQLNCLI', @srvproduct=''
GO
SELECT * FROM OPENQUERY(OQtest, 'test.dbo.select1')

You may also need to adjust security settings on the server for this to work.

Answer 5

You can use the approach described by ck but this is not really recommended. You can check the INSERT-EXEC section of a great post How to Share Data Between Stored Procedures by Erland Sommarskog for more details.