Difference between Stored Proc and User-defined Functions

Today morning my close friend Prashanth (DotNet expert) asked difference between Stored Proc and Functions . So I am dedicating this post to him, also today is bithday of beautiful Buddu, his girlfriend.

Difference betweem Stored Proc and User-defined Functions

• Stored Procedure have pre-compiled execuction plan where as functions do not.
• Functions are used for computations where as procedures are mainly used for performing business logic.
• Functions can only have 'in' parameter where as SP can have both 'in' and 'out' parameters.
• Function does not allow DML (insert, update, delete) queries on an object where as SP allows.
• Functions can be used inline where as SP can't be.
• Stored Procedure can retun more than one value at a time while funtion returns only one value at a time.
• Functions MUST return a value, procedures need not be.

Please leave your comments..

Functions

With my personal experience of SQL Server, I believe Function (User defined functions) are under utilized then their potential so I thought of writing this post in complete honor of UDFs.

Microsoft SQL Server provides three programmable objects
a) User defined Functions b) Stored Procedure c) Triggers

" Functions are pre-prepared pieces of code that may accept parameters, and always return a value"

UDFs support the creation of rich programming logic, including looping, flow control, decision making and branching.
UDFs can be divided into two types based on the value they return
a) Scaler functions b)Table-valued functions

A) Scaler functions: Scalar functions accept 0 or more input parameters and return a single scalar value.
You use the CREATE FUNCTION Transact-SQL statement to create a function. The general syntax of the statement is as follows:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END [ ; ]
::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ EXECUTE_AS_Clause ]
}

Example from AdventureWorks database
CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int])
RETURNS [int]
AS
-- Returns the stock level for the product. This function is used internally only
BEGIN
DECLARE @ret int;

SELECT @ret = SUM(p.[Quantity])
FROM [Production].[ProductInventory] p
WHERE p.[ProductID] = @ProductID
AND p.[LocationID] = '6'; -- Only look at inventory in the misc storage

IF (@ret IS NULL)
SET @ret = 0

RETURN @ret
END;
GO

--Execute
SELECT
p.[ProductID]
,p.[Name]
, dbo.ufnGetStock(p.ProductID) as [Stock]
FROM Production.Product p;

Result


B) Table-Valued Functions: The only difference in table-valued functions is that they return a table as output. Therefore, they are generally used in the FROM clause of a SELECT statement and possibly joined to other tables or views.

Syntax
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS @return_variable TABLE <>
[ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END [ ; ]

Example from AdventureWorks :
CREATE FUNCTION [dbo].[ufnGetContactInfo] (@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
[ContactID] int PRIMARY KEY NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[JobTitle] [nvarchar](50) NULL,
[Email] [nvarchar] (150) Null
)
AS
BEGIN
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT
c.[ContactID]
,c.[FirstName]
,c.[LastName]
,e.[Title] AS [JobTitle]
,c.[EmailAddress]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
WHERE c.[ContactID]= @ContactID;
END;

RETURN;
END;
GO
select * from ufnGetContactInfo(1209)
Result



C) Restrictions on UDFs
Although we can execute virtually any valid batch of code with in function, but still they do have some restrictions:
Most significant is you cannot use a function to change the state of any object in a database or the database itself.Therefore,you cannot insert, update, or delete data in tables, nor can you create, alter, or drop objects in the database. However, you can create one or more table variables and issue INSERT, UPDATE, and DELETE statements against the table variable


Please leave comment...