Potentially delusional co-worker, inline vs. multi statement TVF

Question

Post reply

Potentially delusional co-worker, inline vs. multi statement TVF

sqlnyc

SSCommitted

Points: 1726
More actions
September 3, 2012 at 7:34 pm

#280688

Greetings --
I am having a disagreement with a co-worker about what constitutes a multi-statement Table Valued Function.
A sample call to the function would be something like:
SELECT table1.col1, table1.id, othercol1, othercol2
FROM table1 OUTER APPLY (SELECT * FROM func1(id))
I've stripped out the bulk of the function, and included it below.
Co-worker contends that since there only a single SELECT statement that ultimately returns a type of table, and also because he doesn't specifically define the columns/data types that this must be considered "inline".
Certainly doesn't seem that way to me, and so I thought I'd ask the experts at SqlServerCentral.
I have other questions about this example, but I'll include them in another post.
Thanks for any feedback.
SQLNYC
------------------------------
CREATE FUNCTION [dbo].[func1]
(
@param_x INT
)
RETURNS TABLE
AS
RETURN
(
WITH ReminderEntity ( param1, param2 ) AS
(
SELECT ...
),
ReminderParent ( param_y ) AS
(
SELECT TOP 1 some_col FROM
(
SELECT ...
WHERE
UNION ALL
SELECT ...
OUTER APPLY ( SELECT * FROM func2(param1, param2, param3)) AS some_alias
WHERE some_condition
) AS Temp
),
ReminderParentEntity ( param1, param2 ) AS
(
SELECT ...
),
ReminderParentGuidEntity ( param1, param2 ) AS
(
SELECT ...
)
SELECT TOP 1 EmpID FROM
(
SELECT TOP 1 EmpID
FROM
(
SELECT ...
INNER JOIN
UNION ALL
SELECT ...
INNER JOIN
UNION ALL
SELECT ...
INNER JOIN
UNION ALL
SELECT ...
INNER JOIN
UNION ALL
SELECT ...
INNER JOIN
UNION ALL
SELECT ...
INNER JOIN
UNION ALL
SELECT ...
INNER JOIN
UNION ALL
SELECT ...
INNER JOIN
UNION ALL
SELECT ...
INNER JOIN
UNION ALL
SELECT ...
INNER JOIN
) AS RemindersEmployee
UNION ALL
SELECT TOP 1 ...
FROM some_table
INNER JOIN
INNER JOIN
LEFT JOIN
LEFT JOIN
CROSS JOIN
WHERE some_condition
) AS Temp
)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The skeleton you show us is an inline table valued function.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Lynn Pettis (9/3/2012)
The skeleton you show us is an inline table valued function.

Agreed. The table is not explicitly defined and there is no BEGIN/END pair (just to name a couple of the more obvious giveaways on this one).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlnyc SSCommitted Points: 1726 More actions · Answer 3

sqlnyc

SSCommitted

Points: 1726

September 3, 2012 at 9:37 pm

#1533104

Thanks very much Jeff and Lynn.

Best,

SQLNYC

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

A multi-statement table valued user-defined function looks like this: (example from Books Online)

CREATE FUNCTION dbo.FindReports (@InEmpID INTEGER)

RETURNS @retFindReports TABLE

(

EmployeeID int primary key NOT NULL,

FirstName nvarchar(255) NOT NULL,

LastName nvarchar(255) NOT NULL,

JobTitle nvarchar(50) NOT NULL,

RecursionLevel int NOT NULL

)

AS

BEGIN

INSERT INTO @retFindReports

SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel

FROM ....

RETURN

END;

The key things being the definition of the table variable and one or more inserts into that table variable

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlnyc SSCommitted Points: 1726 More actions · Answer 5

Hi Gail and Joe - thanks so much for your replies.

Just wanted to be clear that this is not my code --

Joe - I completely agree that this database is a disaster. An inherited structure that can not easily be changed, unfortunately.

SQLNYC