Why is selecting from stored procedure not supported in relational databases?

Question

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?

Hyponitrite

You can select from functions and in Oracle (anmd Postgres) you can return a ref-cursor from a procedure, which can be used to return "arbitrary" results. But those can't be used together with e.g. a where clause. – Vedda

postgres you can return data set and use it on select. So I guess is the problem is the person who wrote the spec for those database – Potsherd

Other relational DBs like MySQL do allow stored procs to return results sets, in fact some allow multiple result sets. – Fancyfree

@JuanCarlosOropeza: so can Oracle and SQL Server (but only from functions, not from procedures) – Vedda

@a_horse_with_no_name I think postgres only have functions. When not returning data you can call it storeprocedure? – Potsherd

@JuanCarlosOropeza Does Postgres has stored procedure support or function only? – Hannelorehanner

@lad2025: Postgres has only functions - but you can use them like a table in a select statement. – Vedda

@JuanCarlosOropeza: Yes, Postgres has only functions. But Oracle and SQL also support functions from which you can select – Vedda

Answer 1

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.

Procedures vs. Functions, historically

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.

4GL vs. 3GL languages

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:

• Procedures can call procedures, any function and SQL
• "Pure" functions can call "pure" functions and SQL
• SQL can call "pure" functions and SQL

But:

• "Pure" functions calling procedures become "impure" functions (like procedures)

And:

• SQL cannot call procedures
• SQL cannot call "impure" functions

Examples of "pure" table-valued functions:

Here are some examples of using table-valued, "pure" functions:

Oracle

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))

SQL Server

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)

PostgreSQL

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:

• A table is created
• A record is inserted

Yet:

SELECT * FROM wow();

Yields

wow
---
1

Flamboyant

For what it's worth, that is selecting from a table-valued function and not strictly selecting from a stored procedure per se. – Largo

"I don't really see the point of selecting from stored procedures" - I added a bit more clarification in my question - typically one wants to do this when you are dealing with a pre-existing 3rd party or system stored procedure. It is not an illogical desire, and the same end result can be achieved by jumping through various hoops, so my question is: is there a reason why it is fundamentally not allowed? (And I don't think it's an oversight on behalf of all vendors.) – Hyponitrite

@tbone: I understand your will to make use of pre-existing procedures in SELECT statements, but it makes no sense nonetheless from a conceptual point of view. I'll update my answer, hoping to clarify things... Very interesting question, btw. – Flamboyant

I don't disagree with anything you've written, except maybe "makes no sense nonetheless from a conceptual point of view" would more correct written as "makes no sense nonetheless from an academic point of view". Conceptually and practically, there are numerous reasons to select from a stored procedure - the problem I guess from the vendor perspective is, maybe they insist on ~guaranteeing a "correct" environment, and selecting from a stored procedure is very often going to be "incorrect" (it's also why in TVF's, unlike sp's, you must declare the columns being returned). – Hyponitrite

I added a PostgreSQL example for you at the end. This is what I mean by "what sense does this make!? It's just nuts!!". But technically, you're right :-) – Flamboyant

@Hyponitrite it's also why in TVF's, unlike sp's, you must declare the columns being returned) No you don't have to, check SQL SERVER inline syntax CREATE FUNCTION dbo.func RETURNS TABLE AS RETURN (SELECT * FROM table) – Hannelorehanner

@lad2025: Isn't that just syntax sugar, inferring the table/column declarations implicitly from the returning query? Just like CREATE VIEW x AS SELECT * FROM Y – Flamboyant

@LukasEder Probably it is :) Metadata for table/view are known at compile time. – Hannelorehanner

@lad2025 Not just syntactic sugar, but Inline TVFs are mainly just Views that accept parameters. Not exactly the same as Multi-statement TVFs. – Devisor

@srutzky: So, can they be expanded into the call-site by the query planner, like ordinary views? – Flamboyant

I recall hearing that the idea was that Inline TVFs would indeed be treated like Views in that way, but I haven't been able to prove it. But they are definitely treated differently. You can add a signature to several types of code objects, but not to Inline TVFs. – Devisor

@LukasEder Can PostgreSQL call SQL Server (Oracle, etc) stored procedures easily? Can I call a stored procedure on MSSQL ServerA and join that to a call to stored procedure on MSSQL ServerB? – Hyponitrite

@Hyponitrite Huh, would be interesting to figure out... It's probably possible in one way or another via foreign data wrappers, but I suggest asking a new, specific question for this. Very interesting question! – Flamboyant

Amen, Postgres is beautiful. – Transcendentalism

Answer 2

I don't think your question is really about stored procedures. I think it is about the limitations of table valued functions, presumably from a SQL Server perspective:

• You cannot use dynamic SQL.
• You cannot modify tables or the database.
• You have to specify the output columns and types.
• Gosh, you can't even use rand() and newid() (directly)

(Oracle's restrictions are slightly different.)

The simplest answer is that databases are both a powerful querying language and an environment that supports ACID properties of transactional databases. The ACID properties require a consistent view, so if you could modify existing tables, what would happen when you do this:

select t.*, (select count(*) from functionThatModifiesT()) -- f() modifies "t"
from t;

Which t is used in the from? Actually, SQL Server sort of has answer to this question, but you get the same issue with multiple references in the same clause. In a sense, user defined functions are limited in the same way that this is not accepted:

select a = 1, a + 1

Defining the semantics is very, very tricky and not worth the effort because there are other powerful features that are more important.

In my opinion, though, the final straw in SQL Server is the ability for stored procedures to "return" multiple result sets. That simply has no meaning in the world of tables.

EDIT:

Postgres's use of create function is very powerful. It does allow the function to modify the underlying database, which brings up interesting transactional issues. However, you still do have to define the columns and their types.

Barty

Curious: What's your take on PostgreSQL's capability of doing pretty much anything in functions or CTEs? (e.g. at the end of my answer) – Flamboyant

Asking the same question I asked of Lukas above: Can PostgreSQL call SQL Server (Oracle, etc) stored procedures easily? Can I call a stored procedure on MSSQL ServerA and join that to a call to stored procedure on MSSQL ServerB? I'm trying to find a platform to work from that has the power of SQL but allows you to bend the rules when you want to (as is possible with OPENROWSET on MSSQL) – Hyponitrite

Answer 3

Speaking only for Microsoft SQL Server: Stored Procedures and Functions (at least scalar UDFs and Multi-statement TVFs) are different constructs.

• Stored Procedures are pre-parsed query batches. You execute ad hoc queries batches or query batches saved in Stored Procedures. For example, from .NET there are two different Command Types: Text and StoredProcedure. You cannot just execute a Function.

• Functions, even TVFs (which, if they are Inline-TVFs, are essentially Views that can take parameters) are not independently runnable pieces of code. They are not queries in themselves and hence need to be called within a query.

  Also, unlike Stored Procedures, Functions can be optimized, meaning that they can be moved around the execution plan. The timing and frequency of their execution is not guaranteed to be how you specified in the query (such as, per row vs once and the result cached). In fact, this sometimes causes problems when non-deterministic results are desired but only a single value is returned for all rows. This is probably the main reason (of maybe a few) that Functions do not allow for changing state of the database and some other handy things: because you have no control over whether or not those things would actually happen or in what order, or how many times. Stored Procedures, on the other hand, are the execution plan.

That being said, for what it's worth, it is possible to select from a Stored Procedure without using OPENQUERY / OPENROWSET, but it requires SQLCLR. In fact, most of the restrictions placed on T-SQL Functions can be overcome in SQLCLR code (such as "no Dynamic SQL"). However, this does not make SQLCLR functions immune from the Query Optimizer changing the timing and frequency of the execution from what you want / expect.

Devisor

"it is possible to select from a Stored Procedure....but it requires SQLCLR" - yes, but I then want to JOIN to the result of that. The ulterior motive behind this question is my never-ending pursuit of Data Virtualization in the Microsoft stack. :) Polybase is a slight move in the right direction, PowerQuery/M is another possible route, but ultimately I want the ability in SQL Server to join to any data source. – Hyponitrite

re: "but I then want to JOIN to the result of that" - yes, you can write a specific SQLCLR TVF and join to it, but I want the ability to join to a generic/arbitrary function (sql statement & resultset columns determined at runtime (extracted from metadata)). – Hyponitrite

@Hyponitrite Well, doing this in a generic manner is not going to happen with SQLCLR. OPENQUERY and OPENROWSET are the only means of doing this in a dynamic fashion,unfortunately. – Devisor

Answer 4

My experience is only with SQL Server, and pretty much only anecdotal based on my own usage...but what would you want to accomplish by SELECTing from a stored procedure in the first place? What is your use case?

In my experience, stored procs are the results of what you select, not a source of selection in the first place. You create a stored procedure to return a result set and then in (usually in code) do something with that result set. Or call the stored procedure to do something other than selecting - INSERT or DELETE for example.

If you want to capture results of a query in TSQL and do further somethings with that you could put your SELECT statements in a CTE, or make a view to select from.

Largo

A pre-existing stored procedure (perhaps working in a system from a 3rd party, or system stored procedures). – Hyponitrite

Answer 5

A store procedure while it can and typically does return a result set should be thought of as a device to execute business logic. Views or Table functions should be used to provide the functionality you desire.

Oshiro

Answer 6

In Oracle you can select from stored FUNCTIONS. They are strongly typed so you can treat them as regular subqueries. You typically have to use SELECT FROM TABLE (CAST (function_call(args) AS TABLE_COLL_TYPE))

Also, you can "join" with a stored function by using values from another table as arguments to the function, e.g.

select t1.a, t1.b, func.c
from t1, table (function_call (a, b)) as func

Interrupter