UDFs, Stored Procs, table-valued functions and subqueries

Question

UDFs, Stored Procs, table-valued functions and subqueries

Golfer22

SSCrazy

Points: 2256
More actions
May 13, 2012 at 10:11 am

#274064

I have five questions.
1. Table-valued subqueries are subqueries that return tables while they may or may not accept tables (or table variables) as input parameters, right?
2. Table-valued functions are those that return tables while they may or may not accept tables (or table variables) as input parameters, right?
3. Can a UDF modify and write data (e.g., via schema binding updates)?
4. Can I use a stored procedure in the "FROM" clause in SELECT statement?
5. Can a stored procedure return a table?

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Rowles (5/13/2012)
I have five questions.
1. Table-valued subqueries are subqueries that return tables while they may or may not accept tables (or table variables) as input parameters, right?

They're more commonly referred to as "derived tables" and "Common Table Expressions" (slightly different usage, though) and they don't return tables. They return result sets which can be aliased as if they were tables. The difference is mostly semantic but it's an important difference.

2. Table-valued functions are those that return tables while they may or may not accept tables (or table variables) as input parameters, right?

Again and despite the name, these don't really return tables. They return result sets which can be aliased and used as if they were tables.

3. Can a UDF modify and write data (e.g., via schema binding updates)?

UDF's cannot modify external data and cannot write to tables. They can write to internally created variables and table variables.

4. Can I use a stored procedure in the "FROM" clause in SELECT statement?

Yes, if you use something like OPENQUERY or OPENROWSET.

5. Can a stored procedure return a table?

Again, a bit on the semantic side but no. It can't return a "table". It can, however, return a result set that can be aliased and used as if it were a table or returned to the GUI.

Do I get the job or the "A"? 😉

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Jeff Moden (5/13/2012)
Do I get the job or the "A"? 😉

Now, now, don't be greedy Jeff, just pick one or the other. 😀

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Damned good to see you back, Barry! Really Long time no see!

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Hey, you too, Jeff. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]