Solved

Stored Procedure vs. Function

Posted on 2007-11-16
6
1,208 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:
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

838 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