Function

Definition: a bunch of SQL Statements which can be stored under 1 name.

Type of Function

      · Scalar Function

o Returns a single value

CREATE FUNCTION Fun1 (@a int)

RETURNS int

AS

BEGIN

DECLARE @B int

SET @B = @A

RETURN(@b)

END

      · Table valued Function

o Returns a table (only 1)

CREATE FUNCTION Fun2 (@a int)

RETURNS @temp table (B int)

AS

BEGIN

INSERT INTO @temp (B) VALUES (@A)

RETURN

END

      · Aggregate Function

      · System Function

o Mathematical functions, Date functions, etc…

Difference between SP and Function

• A scalar valued function can be used in SELECT and WHERE clause and a table-valued function can be used in FROM clause. SP cannot be used anywhere
• Function won’t except OUTPUT pram, SP does
• You cannot call an SP, cannot create #TEMP table, cannot use DML and DDL statements inside function. SP will allow.
• Function can be used as User Defined DataType, SP cannot
• You cannot return text, image, timestamp from a function
• Default Param