Function Vs Stored Procedure

Question

Function Vs Stored Procedure

Anjan@Sql

SSCrazy

Points: 2316
More actions
September 9, 2013 at 11:40 pm

#282331

Hi Every one,
We all Knows that we call function through select and Sp through Exec.My Question is why can't we call the Sp
through Select .is there any hidden Reason behind that?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

A function has a fixed output in the sense that the metadata of the columns returned is known upfront. Functions are thus meant to return data.

Stored procedures however can do anything. They are meant to do something, not necessarily return data. If they return data at all, the metadata is not known upfront.

If you want something to return data and use it in a select, then write a fuction.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 2

Yep. Exactly. Nothing hidden there. They are different constructs doing different things.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

Are you asking this because you are curious, or because you want to use the output of a stored procedure as an input for a query? There are ways...

^{“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

Anjan@Sql SSCrazy Points: 2316 More actions · Answer 4

thanks grant frinchy. now i am reading the book sql-server-execution plans by you.extraordinary book for execution palns.

Anjan@Sql SSCrazy Points: 2316 More actions · Answer 5

Anjan@Sql

SSCrazy

Points: 2316

September 10, 2013 at 10:19 pm

#1649093

@chrism-2 thanks.yes really i am curious.....

John Chapman SSC-Addicted Points: 457 More actions · Answer 6

Some more points:

- Functions have some limitations (can't execute stored procedures or use temporary tables for example). Occasionally, this can bite because TTs allow for more flexibility with indexes/statics etc.

- Functions can be called from anywhere whereas procs can't be executed within a Function or Select/View (the "INSERT #TempTable EXEC proc" pattern excepted). This makes functions a more flexible candidate for layered implementation of business-rules.

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 7

John Chapman (9/12/2013)
Some more points:
- Functions have some limitations (can't execute stored procedures or use temporary tables for example). Occasionally, this can bite because TTs allow for more flexibility with indexes/statics etc.
- Functions can be called from anywhere whereas procs can't be executed within a Function or Select/View (the "INSERT #TempTable EXEC proc" pattern excepted). This makes functions a more flexible candidate for layered implementation of business-rules.

However you can use a common table expression in a function.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 8

Ananth@Sql (9/9/2013)
Hi Every one,
We all Knows that we call function through select and Sp through Exec.My Question is why can't we call the Sp
through Select .is there any hidden Reason behind that?

Ah, but you CAN call a stored procedure though a SELECT using OPENROWSET. If you need to pass parameters to the sproc, it becomes a bit of a PITA because you have to use dynamic SQL, but it can be 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)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 9

Jeff Moden (9/13/2013)
Ah, but you CAN call a stored procedure though a SELECT using OPENROWSET. If you need to pass parameters to the sproc, it becomes a bit of a PITA because you have to use dynamic SQL, but it can be done.

But it is an awfully bad thing to do. And it will not work with all stored procedures.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]