Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Stored Procedure vs. Function

Posted on 2007-11-16
6
Medium Priority
?
1,228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 400 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 70

Accepted Solution

by:
Scott Pletcher earned 600 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 70

Expert Comment

by:Scott Pletcher
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

609 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