Difference Between User Defined Function and Stored Procedure

A stored procedure is a program (or procedure) which is physically stored within a database. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

Functions are computed values and cannot perform permanent environmental changed to SQL Server (i.e. no INSERT or UPDATE statements allowed).
A Function can be used inline in SQL Statements if it returns a scalar value or can be joined upon if it returns a result set.

Differences between Stored procedure and User defined functions

Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables.

  UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL.
User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs.

1. UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
2. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
3. Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations. 
4. Unlike Stored Procedure DML operations, like INSERT/UPDATE/DELETE, are not allowed in UDF.
5. A stored procedure can have both input and output parameters  whereas UDF can only have input parameters.
   UDF can be directly selected from, whereas a stored procedure requires one to insert the results into a tempory table or table variabl
   
   
6. Stored procedures different than multi-line TVF is that the optimizer can use statistics to create a better query plan for a stored procedure; however, no statistics are maintained on Multi-line UDFs, so the optimizer has to guess at the number of rows, which can lead to a sub-optimal plan.
7. Stored procs can create a table but can’t return table.
   Functions can create, update and delete the table variable. It can return a table
8. Stored Procedures can affect the state of the database by using insert, delete, update and create operations.
   Functions cannot affect the state of the database which means we cannot perform insert, delete, update and create operations operations on the database.
9. Stored procedures are stored in database in the compiled form.
   Function are parsed and conpiled at runtime only.
10. Stored procs can be called independently using exec keyword. Stored procedure cannot be used in the select/where/having clause.
    Function are called from select/where/having clause. Even we can join two functions. 
11. Stored procedure allows getdate () or other non-deterministic functions can be allowed.
    Function won’t allow the non-deterministic functions like getdate().
12. In Stored procedures we can use transaction statements. We can’t use in functions.
13. The stored procedures can do all the DML operations like insert the new record, update the records and delete the existing records.
    The function won’t allow us to do the DML operations in the database tables like in the stored procedure. It allows us to do only the select operation. It will not allow to do the DML on existing tables. But still we can do the DML operation only on the table variable inside the user defined functions.
14. Temporary tables (derived) can be created in stored procedures.
    It is not possible in case of functions.
15. when sql statements encounters an error, T-SQL will ignore the error in a SPROC and proceed to the next statement in the remaining code.
    In case of functions, T-SQL will stop execution of next statements.

In a function you can’t:
– create temporary tables
– use transactions
– call a stored procedure
– use set commands(example: FORCEPLAN ON, ANSI DEFAULT NULL etc.), and the context inside the function is the same like the one from which is called.
– execute update,insert,delete statement
– have an ouput parameter like stored procedure have(this one can have an workaround no mather you need to return a single value or a set of values)
– multiple results(a stored procedure can return up to 1024, I think)
– call exec(‘some valid sql string’) statement(why should you want? but you can’t)
– use begin try end try begin catch end catch

SQL SERVER – User Defined Functions (UDF) Limitations

May 29, 2007 by pinaldave

UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF can not do and why Stored Procedure are considered as more flexible then UDFs. Stored Procedure are more flexibility then User Defined Functions(UDF).

• UDF has No Access to Structural and Permanent Tables.
  • UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure)
• UDF Accepts Lesser Numbers of Input Parameters.
  • UDF can have upto 1023 input parameters, Stored Procedure can have upto 21000 input parameters.
• UDF Prohibit Usage of Non-Deterministic Built-in Functions
  • Functions GETDATE() etc can not be used UDFs, but can be used in Stored Procedure
• UDF Returns Only One Result Set or Output Parameter
  • Due to this it can be used in SELECT statement but can not return multiple result set like Stored Procedure
• UDF can not Call Stored Procedure
  • Only access to Extended Stored Procedure.
• UDF can not Execute Dynamic SQL or Temporary Tables
  • UDF can not run dynamic SQL which are dynamically build in UDF. Temporary Tables can not be used in UDF as well.
• UDF can not Return XML
  • FOR XML is not allowed in UDF
• UDF does not support SET options
  • SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)
• UDF does not Support Error Handling
  • RAISEERROR or @@ERROR are not allowed in UDFs.

As I said earlier this article is written to show Limitations of UDF. I use UDF for many reasons, the main reason I use it I can do repetitive task in SELECT statement as well as modularizing my frequently used code.

Advantages of UDFs

So why are UDFs important? What can you do with UDFs that you cannot with stored procedures? Well, UDFs are not functionally stronger than stored procedures (in fact, UDFs have many limitations, which we will examine shortly). However, UDFs do provide certain advantages, discussed in the following sections.

Execution Within the SELECT Statement

UDFs are executed within an inline query; for instance, you can call a scalar UDF that calculates the number of business days between two dates, as follows:

SELECT dbo.no_of_business_days(date1, date2)

The developer's ability to execute UDF within inline queries makes UDF easier to use than a procedure within other routines. If you had to perform the same calculation in a procedure, you'd have to return the value as an output parameter. Using output parameters isn't necessarily cumbersome, but would involve more coding, as follows:

DECLARE @no_of_business_days INT

EXEC dbo.proc_for_calculating_business_days date1, date2, @no_of_business_days OUTPUT

Execution from Various Parts of SQL Statements

Scalar UDFs can be called from within the WHERE/HAVING clause of queries or even within a SET list of the UPDATE statement. For instance, the following UDF determines the total sales for a given customer within a particular year by using the Northwind database:

CREATE FUNCTION dbo.total_orders (@customerid CHAR(6), @year INT)
RETURNS INT
AS
BEGIN
DECLARE @total INT

SELECT @total = SUM(unitprice * quantity) 
FROM [order details] a INNER JOIN orders b
ON a.orderid = b.orderid
WHERE 
DATEPART(YEAR, orderdate) = @year
AND
customerid = @customerID


RETURN @total
END

This function can be executed within a WHERE clause of the query returning customers who have purchased goods for more than $15,000 in 1996:

SELECT DISTINCT CustomerID 
FROM 
orders a INNER JOIN [order details] b
ON a.orderid = b.orderid
WHERE 
dbo.total_orders(customerID, 1996) > 15000

Results:

CustomerID

ERNSH

UDFs can also be used within a CASE statement; for instance, you can use total_orders UDF to rank customers according to their sales in 1996, as follows:

SELECT CompanyName, 
SalesRank_1996 = CASE
  WHEN dbo.total_orders(customerID, 1996) < 1000 THEN 'poor'
  WHEN dbo.total_orders(customerID, 1996) BETWEEN 1001 AND 5000 THEN 'average'
  WHEN dbo.total_orders(customerID, 1996) BETWEEN 5001 AND 13000 THEN 'good'
  ELSE 'super'
  END
FROM customers

Results (abbreviated list):

CompanyName                                  SalesRank_1996 
----------------------------------------     -------------- 
Alfreds Futterkiste                          super
Ana Trujillo Emparedados y helados           poor
Antonio Moreno Taquería                      poor
Around the Horn                              average
Berglunds snabbköp                           average
Blauer See Delikatessen                      super
Blondesddsl père et fils                     good
Bólido Comidas preparadas                    poor
Bon app'                                     average
Bottom-Dollar Markets                        average

UDF Output Can Be Used as a Rowset

UDFs that return tables can be treated as another rowset. Thus, you can join other tables within your code module to the output of a UDF. For example, if you have a UDF returning two best-selling titles along with their authors, you can easily join it with another table, as follows:

SELECT 
      a.title, 
      a.author_name, 
      b.royalty_amount
FROM dbo.udf_bestselling_titles a INNER JOIN royalty b
ON a.author_id = b.author_id

If you had to use a stored procedure to do the same procedure, you'd have to do the following:

1. Define a temporary table with the CREATE TABLE statement.
2. Populate the temporary table with the output of the stored procedure.
3. Join the temporary table to other tables.
4. Drop the temporary table at the end of the routine.

Here is the code for a solution that uses a stored procedure instead of a UDF:

CREATE TABLE #temp_table (
    Title_id  INT,
    Title  VARCHAR(200), 
    Author_name VARCHAR(45)
)

INSERT #temp_table (
    title_id, 
    title, 
    author_name )
EXECUTE dbo.usp_bestseller_titles

SELECT 
      a.title, 
      a.author_name, 
      b.royalty_amount
FROM #temp_table a INNER JOIN royalty b
ON a.author_id = b.author_i

UDFs as Parameterized Views

In-line UDFs can be thought of as views that accept parameters. This flavor of UDFs can be used effectively to return different data from the same tables based on the parameter values. For instance, the following UDF will return different categories of titles based on the supplied parameter:

CREATE FUNCTION dbo.udf_category_titles (@category VARCHAR(12))
RETURNS TABLE 
AS
RETURN (
SELECT title, pub_id, price FROM titles 
WHERE 
type = @category)

In-line UDFs can also be used for security—they can return different data based on the user executing the UDF. For instance, the following UDF returns the hire date only if executed by the database owner:

CREATE FUNCTION dbo.udf_get_employee (@p1 INT)
RETURNS TABLE
AS
RETURN
(
SELECT emp_id, fname, lname, 
hire_date = 
CASE WHEN user_name() = 'dbo' THEN CAST(hire_date AS VARCHAR(12)) ELSE 'n/a' END
FROM 
employee 
WHERE job_id = @p1
)

This UDF can be executed as follows:

SELECT * FROM dbo.udf_get_employee(13)

If executed by the database owner, the output is the following:

emp_id          fname                 lname                            hire_date  
---------       --------------------  ------------------------------   ------------ 
PMA42628M       Paolo                 Accorti                          Aug 27 1992 
TPO55093M       Timothy               O'Rourke                         Jun 19 1988 
CGS88322F       Carine                Schmitt                          Jul 7 1992

If executed by any other user, the output will contain "n/a" in the "hire_date" column.

Multi-Statement Functions: Alternatives to Stored Procedures

Multi-statement functions allow you to perform additional logic, such as variable declaration, populating of table variables, and updating values within it, looping through records and more. Multi-statement functions are a great way to rewrite a stored procedure if the following conditions are true:

• The output of the stored procedure can be used by other routines.
• Stored procedures don't make any data changes in the permanent tables.
• The only purpose of the stored procedure is to create a result set that might have to be manipulated prior to returning it to the user.
• Stored procedures do not perform any tasks prohibited inside the UDFs (see the next section for details).

SQL SERVER – Stored Procedures Advantages and Best Advantage

April 13, 2007 by pinaldave

There are many advantages of Stored Procedures. I was once asked what do I think is the most important feature of Stored Procedure? I have to pick only ONE. It is tough question.
I answered : Execution Plan Retention and Reuse (SP are compiled and their execution plan is cached and used again to when the same SP is executed again)

Not to mentioned I received the second question following my answer : Why? Because all the other advantage known (they are mentioned below) of SP can be achieved without using SP. Though Execution Plan Retention and Reuse can only be achieved using Stored Procedure only.

• Execution plan retention and reuse
• Query auto-parameterization
• Encapsulation of business rules and policies
• Application modularization
• Sharing of application logic between applications
• Access to database objects that is both secure and uniform
• Consistent, safe data modification
• Network bandwidth conservation
• Support for automatic execution at system start-up
• Enhanced hardware and software capabilities
• Improved security
• Reduced development cost and increased reliability
• Centralized security, administration, and maintenance for common routines

Advantages and Disadvantages of Stored Procedure

http://mukund.wordpress.com/2005/10/14/advantages-and-disadvantages-of-stored-procedure/