Solved

Stored Procedure vs. Function

Posted on 2007-11-16
6
1,203 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

776 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