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>

….

<statement_for_procedures>

Code Sample:

CREATE PROCEDURE salesByCategory

@categoryName nvarchar(15),

@orderYr int = 2011

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’)

Rate this post