Solved

Stored Procedure vs. Function

Posted on 2007-11-16
6
1,221 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 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:
Scott Pletcher 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 69

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

717 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