Reuse queries and their results in SQL Server via Function

Question

I have a query that should be reused in many scenarios. This query receives some parameters. Because it has to be reused, it can't be a stored procedure. So, it's created as a Function (not a View, because it needs some parameters). This is the best approach so far, right? The issue is that this query returns data that needs some post processing, i.e. reused in some other queries. I'm facing the issue about reusing them in other queries.

Example: Function GetMyFirstData returns several columns, including a FootNoteSymbol column. I should create another Function (GetFootnoteText) to return the text (and some other details) about these footnotes. How should I create the second function that will receive as a parameter the FootNoteSymbol (many) returned by the first function GetMyFirstData?

I'm avoiding Stored Procedure, because these results will most likely be reused in other queries. Also, the FootNoteSymbol is also returned in many other functions, with different return structures (therefore I can't create a TableType, because the structure is not fixed - however FootNoteSymbol is common among all of them).

Using SQL Server 2008 R2.

Functions that return data:

CREATE FUNCTION GetMyFirstData
(
  @Param1 int,
  @Param2 int
)
RETURNS @Return TABLE
(
  Col1 int,
  Col2 int,
  FootnoteSymbol int,
  Col3 int,
  Col4 int
)
AS
  BEGIN
    SELECT Col1, Col2, FootnoteSymbol, Col3, Col4
    FROM MyData
    RETURN;
  END

CREATE FUNCTION GetMySecondData
(
  @Param1 int,
  @Param2 int
)
RETURNS @Return TABLE
(
  Col1 int,
  FootnoteSymbol int,
  Col2 int
)
AS
  BEGIN
    SELECT Col1, FootnoteSymbol, Col2
    FROM MyOtherData
    RETURN;
  END

Function that should get footnotes text:

CREATE FUNCTION GetFootnoteText
(
  @FootnoteSymbol --this is the issue, how to reuse the footnotesymbols from the other functions
)
RETURNS @Return TABLE
(
  Symbol int,
  Text text,
  OtherDetail nvarchar(200)
)
AS
  BEGIN
    SELECT Symbol, Text, OtherDetail
    FROM MyFootnotes
    WHERE Symbol in --this is the issue, how to reuse the footnotesymbols from the other functions
    RETURN;
  END

Thanks!

Answer 1

As far as I can tell (and I reference you to @SeanLange comment "You know what your tables look like, what the data is like, what the rules are and what the expected results are. I on the other hand can't see any of that.") you have a basic miss-understanding about how relational databases work. To "solve" the problem presented here using standard relational database practices I would not split it up into multiple functions (as there is no gain there) instead I would create a SP that did a JOIN to get all the data you need. Like this:

CREATE PROCEDURE GetData
(
  @Param1 int,
  @Param2 int
)
AS
  BEGIN
    SELECT MyData.Col1,
           MyData.Col2,
           MyFootnotes.Text, 
           MyFootnotes.OtherDetail, 
           MyData.Col3,
           MyData.Col4
    FROM MyData
    JOIN MyFootnotes ON MyData.FootnoteSymbol = MyFootnotes.Symbol
  END

You don't show how you use the parameters so I can't address that, but I can guess. Let's say the parameters in this function are used in the where clause to limit the results. (Col1=@Param1 and Col2=@Param2) but in another case you have different limits (eg Col3=@Param1 and Col4=@Param2).

In this case the best way to do it is to make a view that is shared and limited in each SP. I would not use functions as I see no value to them (and a high potential for problems as @RemusRusanu points out). Like this:

CREATE VIEW MyData AS
    SELECT MyData.Col1,
           MyData.Col2,
           MyFootnotes.Text, 
           MyFootnotes.OtherDetail, 
           MyData.Col3,
           MyData.Col4
    FROM MyData
    JOIN MyFootnotes ON MyData.FootnoteSymbol = MyFootnotes.Symbol

with

CREATE PROCEDURE GetData1
(
  @Param1 int,
  @Param2 int
)
AS
  BEGIN
    SELECT * 
    FROM MyData
    WHERE  MyData.Col1,
           MyData.Col2,
           MyFootnotes.Text, 
           MyFootnotes.OtherDetail, 
           MyData.Col3,
           MyData.Col4
    FROM MyData
    WHERE Col1=@Param1 and Col2=@Param2
  END

and

CREATE PROCEDURE GetData2
(
  @Param1 int,
  @Param2 int
)
AS
  BEGIN
    SELECT * 
    FROM MyData
    WHERE  MyData.Col1,
           MyData.Col2,
           MyFootnotes.Text, 
           MyFootnotes.OtherDetail, 
           MyData.Col3,
           MyData.Col4
    FROM MyData
    WHERE Col3=@Param1 and Col4=@Param2
  END

I know that as a programmer who has worked in non-relational systems this is not intuitive. However trust me, this will get you the best results. This is how your server software expects to be used and over the years it it has been tuned to deliver you fast results using a view in this way.

Answer 2

DO. NOT. DO. THIS.

Reusing code is a noble goal, but SQL is not the language for it. There are many documented performance problems resulting from your approach. Some quick links Query Performance and multi-statement table valued functions, Improving query plans with the SCHEMABINDING option on T-SQL UDFs or Compute Scalars, Expressions and Execution Plan Performance.

I wish I had a good alternative for you, but I don't. Views are OK for query re-use. But attempting to compose SQL table value functions has always ended in disaster, in every engagement I've seen.

Don't do it.

At the very least stick to Inline Table Value Functions;

• The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable, because it is set by the format of the result set of the SELECT statement in the RETURN clause.
• There is no function_body delimited by BEGIN and END.
• The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.
• The table-valued function accepts only constants or @local_variable arguments