It is well known that you cannot perform a SELECT
from a stored procedure in either Oracle or SQL Server (and presumably most other mainstream RDBMS products).
Generally speaking, there are several obvious "issues" with selecting from a stored procedure, just two that come to mind:
a) The columns resulting from a stored procedure are indeterminate (not known until runtime)
b) Because of the indeterminate nature of stored procedures, there would be issues with building database statistics and formulating efficient query plans
As this functionality is frequently desired by users, a number of workaround hacks have been developed over time:
http://www.club-oracle.com/threads/select-from-stored-procedure-results.3147/
http://www.sommarskog.se/share_data.html
SQL Server in particular has the function OPENROWSET
that allows you to join to or select from almost anything: https://msdn.microsoft.com/en-us/library/ms190312.aspx
....however, DBA's tend to be very reluctant to enable this for security reasons.
So to my question: while there are some obvious issues or performance considerations involved in allowing joins to or selects from stored procedures, is there some fundamental underlying technical reason why this capability is not supported in RDBMS platforms?
EDIT:
A bit more clarification from the initial feedback....yes, you can return a resultset from a stored procedure, and yes, you can use a (table valued) function rather than a stored procedure if you want to join to (or select from) the resultset - however, this is not the same thing as JoiningTo / SelectingFrom a stored procedure. If you are working in a database that you have complete control over, then you have the option of using a TVF. However, it is extremely common that you find yourself working in a 3rd party database and you are forced to call pre-existing stored procedures; or, often times you would like to join to system stored procedures such as: sp_execute_external_script (https://msdn.microsoft.com/en-us/library/mt604368.aspx).
EDIT 2:
On the question of whether PostgreSQL can do this, the answer is also no: Can PostgreSQL perform a join between two SQL Server stored procedures?
We can not directly use stored procedures in a SELECT statement.
You cannot execute a stored procedure inside a function, because a function is not allowed to modify database state, and stored procedures are allowed to modify database state.
TL;DR: you can select from (table-valued) functions, or from any sort of function in PostgreSQL. But not from stored procedures.
Here's an "intuitive", somewhat database-agnostic explanation, for I believe that SQL and its many dialects is too much of an organically grown language / concept for there to be a fundamental, "scientific" explanation for this.
I don't really see the point of selecting from stored procedures, but I'm biased by years of experience and accepting the status quo, and I certainly see how the distinction between procedures and functions can be confusing and how one would wish them to be more versatile and powerful. Specifically in SQL Server, Sybase or MySQL, procedures can return an arbitrary number of result sets / update counts, although this is not the same as a function that returns a well-defined type.
Think of procedures as imperative routines (with side effects) and of functions as pure routines without side-effects. A SELECT
statement itself is also "pure" without side-effects (apart from potential locking effects), so it makes sense to think of functions as the only types of routines that can be used in a SELECT
statement.
In fact, think of functions as being routines with strong constraints on behaviour, whereas procedures are allowed to execute arbitrary programs.
Another way to look at this is from the perspective of SQL being a 4th generation programming language (4GL). A 4GL can only work reasonably if it is restricted heavily in what it can do. Common Table Expressions made SQL turing-complete, yes, but the declarative nature of SQL still prevents its being a general-purpose language from a practical, every day perspective.
Stored procedures are a way to circumvent this limitation. Sometimes, you want to be turing complete and practical. So, stored procedures resort to being imperative, having side-effects, being transactional, etc.
Stored functions are a clever way to introduce some 3GL / procedural language features into the purer 4GL world at the price of forbidding side-effects inside of them (unless you want to open pandora's box and have completely unpredictable SELECT
statements).
The fact that some databases allow for their stored procedures to return arbitrary numbers of result sets / cursors is a trait of their allowing arbitrary behaviour, including side-effects. In principle, nothing I said would prevent this particular behaviour also in stored functions, but it would be very unpractical and hard to manage if they were allowed to do so within the context of SQL, the 4GL language.
Thus:
But:
And:
Here are some examples of using table-valued, "pure" functions:
CREATE TYPE numbers AS TABLE OF number(10);
/
CREATE OR REPLACE FUNCTION my_function (a number, b number)
RETURN numbers
IS
BEGIN
return numbers(a, b);
END my_function;
/
And then:
SELECT * FROM TABLE (my_function(1, 2))
CREATE FUNCTION my_function(@v1 INTEGER, @v2 INTEGER)
RETURNS @out_table TABLE (
column_value INTEGER
)
AS
BEGIN
INSERT @out_table
VALUES (@v1), (@v2)
RETURN
END
And then
SELECT * FROM my_function(1, 2)
Let me have a word on PostgreSQL.
PostgreSQL is awesome and thus an exception. It is also weird and probably 50% of its features shouldn't be used in production. It only supports "functions", not "procedures", but those functions can act as anything. Check out the following:
CREATE OR REPLACE FUNCTION wow ()
RETURNS SETOF INT
AS $$
BEGIN
CREATE TABLE boom (i INT);
RETURN QUERY
INSERT INTO boom VALUES (1)
RETURNING *;
END;
$$ LANGUAGE plpgsql;
Side-effects:
Yet:
SELECT * FROM wow();
Yields
wow
---
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With