Can you make a selection based on the results of a stored procedure in T-SQL

Question

select * from (EXEC sp_SomeStoredProc) 

If you cannot do this, then what causes it to not be added to the SQL or T-SQL standard?

Answer 1

You cannot do this, but you can do it as an insert. eg.

 insert mytable exec myStoredProcedure 

Also, never name the sp_xxxx stored procedures. This is because SQL will always search in the area of ​​the system stored procedure because of sp_ before searching in user-defined stored procedures, which will result in a small performance loss, which can add it to a rather significant process that is often performed.

Answer 2

This is possible, but certainly not the right way:

 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 configure the security settings on the server for this to work.

Answer 3

What if the stored procedure does not return rows? Lots of result sets? Changes? The potential uses of the stored procedure are many and varied.

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

Rather, in a relational sense, a stored procedure is not a relation / table, so you cannot select it.

Custom functions achieve what you want, but allow the code to conform to a specific relation / table concept.

Answer 4

You cannot do this, but you can consider the function in sqlserver2005. Here is 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 just select from the function ...

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

And you will get the value of the table.

Answer 5

You can use the approach described by ck, but this is not recommended. You can check the INSERT-EXEC section of the excellent record How to do Share data between Erland Sommarskog stored procedures for more details.