Why Is Selecting from Stored Procedure Not Supported in Relational Databases

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

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

Why does this stored procedure work with one query but not another?

The problem in your code most likely has to do with this loop: WHILE CHARINDEX(',',@DIESECTION)<>0. Since without a comma, it will not do anything.

However the real problem of splitting delimited strings can be solved with some other much faster solutions.

One example is a table valued function by Jeff Moden:

create function [dbo].[delimitedsplit8K] (
      @pstring varchar(8000)
    , @pdelimiter char(1)
  )
returns table with schemabinding as
 return
  with e1(N) as (
    select 1 union all select 1 union all select 1 union all 
    select 1 union all select 1 union all select 1 union all 
    select 1 union all select 1 union all select 1 union all select 1
  )
  , e2(N) as (select 1 from e1 a, e1 b)
  , e4(N) as (select 1 from e2 a, e2 b)
  , ctetally(N) as (
    select top (isnull(datalength(@pstring),0)) 
      row_number() over (order by (select null)) from e4
  )
  , ctestart(N1) as (
    select 1 union all
    select t.N+1 from ctetally t where substring(@pstring,t.N,1) = @pdelimiter
  )
  , ctelen(N1,L1) as (
    select s.N1,
      isnull(nullif(charindex(@pdelimiter,@pstring,s.N1),0)-s.N1,8000)
    from ctestart s
  )
 select itemnumber = row_number() over(order by l.N1)
      , item       = substring(@pstring, l.N1, l.L1)
   from ctelen l
;
go

Which can be used like so:

declare @diesection varchar(8000) = '1,2,3';
select s.ItemNumber, s.Item
from [dbo].[delimitedsplit8K](@diesection,',') s

rextester demo: http://rextester.com/XJSQUS89642

returns:

+------------+------+
| ItemNumber | Item |
+------------+------+
|          1 |    1 |
|          2 |    2 |
|          3 |    3 |
+------------+------+

---

splitting strings reference:

• Tally OH! An Improved SQL 8K “CSV Splitter” Function - Jeff Moden
• Split strings the right way – or the next best way - Aaron Bertrand
• Splitting Strings : A Follow-Up - Aaron Bertrand
• Splitting Strings : Now with less T-SQL - Aaron Bertrand
• string_split() in SQL Server 2016 : Follow-Up #1 - Aaron Bertrand

---

Since this is for reporting, and you are doing things where parameters may or may not be populated, you may find these articles helpful as well:

Catch-all queries reference:

• Parameter Sniffing, Embedding, and the RECOMPILE Options - Paul White
• Dynamic Search Conditions - Erland Sommarskog
• Catch-all queries - Gail Shaw
• An Updated "Kitchen Sink" Example - Aaron Bertand

Can PostgreSQL perform a join between two SQL Server stored procedures?

1. Can PostgreSQL perform a join between two ~procedures where the
   columns are not known until runtime?

The basic answer is simple because there currently are no stored procedures in Postgres (up to Postgres 10), just functions - which provide almost but not quite the same functionality, as you have laid out in the question.

And any function can be used in the FROM clause of a SELECT query like any other table.

Update:

SQL procedures ("stored procedures") are introduced with Postgres 11.

The manual for CREATE PROCEDURE.

SQL itself demands to know the return type at runtime. There is a border-case: you can declare the return type with the function call using polymorphic types. Detailed instructions here (the last chapter being most relevant to you):

• Refactor a PL/pgSQL function to return the output of various SELECT queries

2. Can it do the same, except using stored procedures that reside in
   an external 3rd party database (perhaps via foreign data wrappers or
   some other mechanism)?

That's a NO, too, based on the same principle. If you use foreign tables, you must provide a clearly defined return type one or the other way.

You might be able to lump the whole row resulting from an SQL-Server-stored-procedure into a single tab-delimited text representation, but then (besides being error-prone and inefficient) you have a single column and need the meta information defining individual columns one or the other way to extract columns - catch 22.

Query not working as expected in stored procedure

Try using GROUP BY:

Select *  From callforwarding 
Where  Mastercode in (select SRFID from @SRFTable)
    and ApproverNo = @empID
    and ForwardDate in 
               (Select max(ForwardDate) from CallForwarding
                where Mastercode in (select SRFID from @SRFTable)
                and ApproverNo = @empID
                group by Mastercode)

Stored procedure not returning any data for the date passed

because your u_datetime column contains date & time

one way is to handle in the WHERE clause

and a.U_datetime >= @As_ONDATE
AND a.U_datetime <  DATEADD(DAY, 1, @As_ONDATE)

Also you might want to consider converting your query not to use Cursor

---

Related Topics