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:
- 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.
- SP can return none or zero, one or N number of values.
- SP are stored in parsed and compiled state in the Database.
- SP can have input and output parameters.
- Exception handling can be done in SP i.e. try catch block.
- We can call a Function from within a SP.
- SP cannot be called using the Select statement.
- 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:
- We cannot use DML statements in Functions.
- Functions must return at least one value.
- Functions are compiled and executed at runtime.
- Functions can have only output parameters.
- Exception handling cannot be done in Functions.
- We cannot call SP from within a Function.
- Functions can be called using Select statement and can be used in Where or Having clause as well.
There are 2 types of Functions:
- Build in System Functions e.g. Avg(), Count(), Sum(), Getdate(), Lower(), Upper(), Isnull() etc.
- User Defined Functions (UDF)
Furthermore there are 2 types of UDF:
- Scalar-valued Functions
- 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:
- Inline Table UDF
- 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