Creating and Using Inline Table-Valued Functions

Question

Creating and Using Inline Table-Valued Functions

kimani_m

SSC Enthusiast

Points: 188
More actions
September 8, 2020 at 8:11 pm

#373561

Comments posted to this topic are about the item Creating and Using Inline Table-Valued Functions

Viewing 13 posts - 1 through 12 (of 12 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Excellent article, very thorough. Thanks for sharing this.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sqlfriend SSC Guru Points: 52776 More actions · Answer 2

Thanks for the great article! Very through and easy understand!

Can someone change my rate to 5 stars instead of one, not sure how to use the rate star, when I clicked the first star without having time to click next, it jumped to the end, so it ended one star.

kimani_m SSC Enthusiast Points: 188 More actions · Answer 3

kimani_m

SSC Enthusiast

Points: 188

January 22, 2019 at 11:13 am

#2019693

Thank you <a title="Go to ChrisM@Works.

kimani_m SSC Enthusiast Points: 188 More actions · Answer 4

Glad you found it easy to understand sqlfriends. Thank you.

Eirikur Eiriksson SSC Guru Points: 182901 More actions · Answer 5

Eirikur Eiriksson

SSC Guru

Points: 182901

January 22, 2019 at 12:31 pm

#2019713

Thanks for this nice write-up Kimani, good job!
😎

kimani_m SSC Enthusiast Points: 188 More actions · Answer 6

kimani_m

SSC Enthusiast

Points: 188

January 22, 2019 at 12:44 pm

#2019719

Thanks Eirikur. Hope you found it useful.

Jeff Moden SSC Guru Points: 1004241 More actions · Answer 7

Agreed with the others. Nice job on the article, Kimani .

BTW/FYI... you CAN actually call a stored procedure from within a function using OPENROWSET. Because of OPENROWSET (it won't take variables) and the inability to use dynamic SQL in a function (although I'll bte someone even figured a way past that), it has some fairly limited uses but for those uses it works a treat.

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

kimani_m SSC Enthusiast Points: 188 More actions · Answer 8

Thank you Jeff and also, thank you for pointing out that you can certainly call a stored procedure within a function.
I ran the following script on my Dev machine and it certainly worked a treat.

USE StackOverflow2010;
GO
sp_configure 'show advanced options',1
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE
GO
CREATE OR ALTER PROCEDURE dbo.NoddyProc
AS
BEGIN
SELECT 1 AS Num;
END
GO
CREATE OR ALTER FUNCTION dbo.itvfnNoddyFunction ()
RETURNS TABLE
AS RETURN (
SELECT np.Num
FROM OPENROWSET('SQLOLEDB','Server=(local);Initial catalog=StackOverflow2010;Trusted_Connection=yes;',
'EXECUTE dbo.NoddyProc') AS np
);
GO
SELECT Num
FROM dbo.itvfnNoddyFunction();
GO

Jeff Moden SSC Guru Points: 1004241 More actions · Answer 9

kimani_m - Wednesday, January 23, 2019 7:36 AM
Thank you Jeff and also, thank you for pointing out that you can certainly call a stored procedure within a function.
I ran the following script on my Dev machine and it certainly worked a treat.
USE StackOverflow2010;
GO
sp_configure 'show advanced options',1
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE
GO
CREATE OR ALTER PROCEDURE dbo.NoddyProc
AS
BEGIN
SELECT 1 AS Num;
END
GO
CREATE OR ALTER FUNCTION dbo.itvfnNoddyFunction ()
RETURNS TABLE
AS RETURN (
SELECT np.Num
FROM OPENROWSET('SQLOLEDB','Server=(local);Initial catalog=StackOverflow2010;Trusted_Connection=yes;',
'EXECUTE dbo.NoddyProc') AS np
);
GO
SELECT Num
FROM dbo.itvfnNoddyFunction();
GO

Well Done!

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

David McKinney SSChampion Points: 10472 More actions · Answer 10

Is the definition of schemabinding not backwards? i.e. Surely it prevents changes to the underlying objects and not the function?

kimani_m SSC Enthusiast Points: 188 More actions · Answer 11

Hi David. Thanks for pointing that out and as you mentioned, schemabinding will prevent changes to the underlying objects, rather than the function. A better BOL definition can be found here (under schemabinding), rather than the previously referenced one.

I've attached a script(itvf_schemabinding) in case someone would like to observe that behaviour.

Cheers. Kimani

This reply was modified 4 years, 3 months ago by kimani_m.

kimani_m SSC Enthusiast Points: 188 More actions · Answer 12

DROP TABLE IF EXISTS dbo.please_remove;
GO

CREATE TABLE dbo.please_remove (
first_col int NOT NULL
);
GO
INSERT INTO dbo.please_remove
(first_col)
VALUES
(1);
GO

CREATE OR ALTER FUNCTION [dbo].[itv_fn_please_remove] (@id int)
RETURNS TABLE 
WITH SCHEMABINDING AS 
RETURN (
SELECT @id AS id
FROM dbo.please_remove
);
GO

--1. Function returns expected result e.g. 1
SELECT Id
FROM [dbo].[itv_fn_please_remove] (1);
GO

--2. Dropping underlying objects fails due to schema binding reference
DROP TABLE dbo.please_remove; 
GO

--3. Dropping underlying objects succeeded only after dropping binding i.e. dropping the function first

--DROP FUNCTION dbo.itv_fn_please_remove;
--GO
--DROP TABLE dbo.please_remove;
--GO