What are the Differences Between Stored Procedures and Functions?

Stored Procedure (SP):

SP is just like a mini program in SQL Server that is physically stored in the Database. Some of the properties of SP are:

1. We can use both DDL (data definition language e.g. Create Table statement) and DML (data manipulation language i.e. Insert, Update and Delete) statements in SP.
2. SP can return none or zero, one or N number of values.
3. SP are stored in parsed and compiled state in the Database.
4. SP can have input and output parameters.
5. Exception handling can be done in SP i.e. try catch block.
6. We can call a Function from within a SP.
7. SP cannot be called using the Select statement.
8. There can be any number of SQL statements within a SP and we can also use cursors in SP.

Functions:

Functions are just like methods in any programming language e.g. C Sharp .NET that is made to do some operation in SQL Server. Some of the properties of Functions are:

1. We cannot use DML statements in Functions.
2. Functions must return at least one value.
3. Functions are compiled and executed at runtime.
4. Functions can have only output parameters.
5. Exception handling cannot be done in Functions.
6. We cannot call SP from within a Function.
7. Functions can be called using Select statement and can be used in Where or Having clause as well.

There are 2 types of Functions:

1. Build in System Functions e.g. Avg(), Count(), Sum(), Getdate(), Lower(), Upper(), Isnull() etc.
2. User Defined Functions (UDF)

Furthermore there are 2 types of UDF:

1. Scalar-valued Functions
2. Table-valued Functions

Scalar-valued Functions:

This type of functions returns only one value and can take none or zero or any number of input parameters e.g.

CREATE FUNCTION YearOnly
(
- Add the parameters for the function here
@DateOfBirth DATE
)
RETURNS INT
AS
BEGIN
DECLARE @YearofDateOfBirth INT
SET @YearofDateOfBirth = (SELECT DATEPART(YEAR, @DateOfBirth))
- Return the result of the function
RETURN @YearofDateOfBirth
END
Above Function can be called as:
SELECT dbo.YearOnly(GETDATE())

Note: Above statement returns only the year part of the current date.

Table-valued Functions:

There are 2 types of Table-valued Functions:

1. Inline Table UDF
2. Multi Statement UDF

Inline Table UDF:

This type of functions returns single Table variable and can take none or zero or any number of input
parameters e.g.

CREATE FUNCTION ListStudentsByFirstName
(
- Add the parameters for the function here
@FirstName VARCHAR(MAX)
)
RETURNS TABLE
AS
- Return the result of the function
RETURN SELECT * FROM Students WHERE FirstName LIKE '%'+@FirstName+'%'
Above Function can be called as:
SELECT * FROM dbo.ListStudentsByFirstName('f')
Or
SELECT StudentID, FirstName FROM dbo.ListStudentsByFirstName('M')

Note: Above statement returns all the students having first name like 'f'. These types of Table-valued
Functions contain only one Select statement. We can also use specific column fields while executing
Table-valued Functions as shown above.

Multi Statement UDF:

This type of functions returns single Table variable and can take none or zero or any number of input parameters. We can use DML statements in Table-valued Multi Statement UDF as shown in the example below:

ALTER FUNCTION MultiStudentsByFirstName
(
 -Add the parameters for the function here
@FirstName VARCHAR(MAX)
)
RETURNS @Students TABLE (SID INT, FName VARCHAR(100))
AS
BEGIN
INSERT INTO @Students (SID, FName)
SELECT StudentID, FirstName
FROM Students WHERE FirstName LIKE '%'+@FirstName+'%'
UPDATE @Students SET FName = 'Faheem Ahmad' WHERE FName = 'faheem'
- Return the result of the function
RETURN
END