Solved

Stored Procedure vs. Function

Posted on 2007-11-16
6
1,194 Views
Last Modified: 2012-05-05
Is there any cut-and dry rule regarding performance between using a stored procedure vs. a function. I need to pass a parameter and return a single scalar value.

I can code it as a stored procedure that has one input and one output parameter, or as a function that accepts a value and returns value.
0
Comment
Question by:dbbishop
  • 2
  • 2
  • 2
6 Comments
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 100 total points
ID: 20300923
found this - credits at bottom of whom wrote article
In many instances you can accomplish the same task using either a stored procedure or a function. Both functions and stored procedures can be custom defined and part of any application. Functions, on the other hand, are designed to send their output to a query or T-SQL statement. For example, User Defined Functions (UDFs) can run an executable file from SQL SELECT or an action query, while Stored Procedures (SPROC) use EXECUTE or EXEC to run. Both are instantiated using CREATE FUNCTION.

To decide between using one of the two, keep in mind the fundamental difference between them: stored procedures are designed to return its output to the application. A UDF returns table variables, while a SPROC can't return a table variable although it can create a table. Another significant difference between them is that UDFs can't change the server environment or your operating system environment, while a SPROC can. Operationally, when T-SQL encounters an error the function stops, while T-SQL will ignore an error in a SPROC and proceed to the next statement in your code (provided you've included error handling support). You'll also find that although a SPROC can be used in an XML FOR clause, a UDF cannot be.

If you have an operation such as a query with a FROM clause that requires a rowset be drawn from a table or set of tables, then a function will be your appropriate choice. However, when you want to use that same rowset in your application the better choice would be a stored procedure.

There's quite a bit of debate about the performance benefits of UDFs vs. SPROCs. You might be tempted to believe that stored procedures add more overhead to your server than a UDF. Depending upon how your write your code and the type of data you're processing, this might not be the case. It's always a good idea to text your data in important or time-consuming operations by trying both types of methods on them.


--------------------------------------------------------------------------------


Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 150 total points
ID: 20301621
I suggest using a function when possible.  For a single use, performance of a properly written function won't differ that much from a sp.  However, if you need to call the code for, say, every row, a function will perform *vastly* better, because you won't have to go row by row to call a sp.
0
 
LVL 15

Author Comment

by:dbbishop
ID: 20302007
Scott,

I know I closed this, but something in your answer 'puzzles' me. If I have a udf, and the following query:

SELECT col1, col2, dbo.myFunction(col1)
FROM myTable

Is this not going to call the function for every row?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 20302075
Yes, you're right, it will.  But to call a sp for every row, *you* have to page thru the rows yourself.  In other words, you *can't* do this:

SELECT col1, col2, (EXEC myProc col1)
FROM myTable

If you want to exec myProc for every column col1, you have to use a cursor or the equivalent, like so:

CREATE TABLE #results (
    col1 INT,
    col2 INT,
    procResult VARCHAR(30)
)
DECLARE csrMyTable CURSOR FAST_FORWARD FOR
SELECT col1, col2
FROM myTable
DECLARE @col1 INT
DECLARE @col2 INT
DECLARE @procResult VARCHAR(30)
OPEN csrMyTable
FETCH NEXT FROM csrMyTable INTO @col1, @col2
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC myProc @col1, @procResult OUTPUT
    INSERT INTO #results
    SELECT @col1, @col2, @procResult
    FETCH NEXT FROM csrMyTable INTO @col1, @col2
END --WHILE
CLOSE csrMyTable
SELECT *
FROM #results

YIKES!
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 20302152
wow Thanks for the points.....
0
 
LVL 15

Author Comment

by:dbbishop
ID: 20303140
Okay. Yes, I knew you can't execute a procedure within a select. Yes, yikes is correct.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now