Stored Procedures and Functions

Difference between Stored Procedures and Functions
 
Stored procedures are a set of actions  already written  and stored inside database  for achieving a particular task.
Functions are general databse objects which are  used for general purpose programming.     
 
S.No.                                  Stored Procedures                                             Functions
 1.  Procedures may return none or more values  Functions must always return one value either a scalar value or a table
 2.  Procedures have input ,output paramaters  Functions have only input parameters
 3.  Stored procedures can be called independently.

For example :  EXEC <Stored Proc Name>  command in SQL Server, or

Call <Stored Proc name> in IBM DB2

 Functions can not be called independently and are called from within SQL statement(s).
 4.  Procedures cannot be called from functions  Functions can be called from procedures
 5.  Exception can be handled in Procedure by try-catch block  try-catch block cannot be used in functions
 6.  Transaction Management is possible in procedures  Transaction management is not possible in functions
 7.  Stored procedures cannot be used inline with a select statement  Functions can be used inline with a select statement
 8.  Procedures are used for executing business logic  Functions are normally used for computation
 9.  Stored procedure is a precompiled execution plan  Functions are not precompiled
 
Creating Stored procedures (SQL Server)
 
CREATE PROCEDURE <procedure_name>
@<param_name1> <data_type> = <default_value1>
@<param_name2> <data_type> = <default_value2>
….
As
<statement_for_procedures>
 
Code Sample:
 
CREATE PROCEDURE salesByCategory
@categoryName nvarchar(15),
@orderYr int = 2011
As
SELECT productName, articleNo
FROM products p, categories c,order o
WHERE p.productId = c.productId
and o.orderYr = @orderYr
and c.categoryName = @categoryName
 
Calling stored procedure
EXEC SalesByCategory ‘Seafood’, 2012
Creating Functions(SQL Server)

CREATE FUNCTION <func_name>(@<param_name> <data_type>) RETURNS TABLE                                                                            AS                                                                                                 RETURN                                                                                         <statemen_for_functions>

Code Sample

CREATE FUNCTION dbo.LookByFName(@FirstLetter char(1))
RETURNS TABLE
AS
RETURN SELECT *
FROM employee
WHERE LEFT(fname, 1) =  @FirstLetter

Using function

SELECT * FROM dbo.LookByFName(‘A’)

 

 
Stored Procedures and Functions
Rate this post

Leave a Reply