Functions in sql server
function is like a stored procedure it include a return statement
to return a result as a single value or as a table
it is a block of code we can reuse it
it takes only input parameters

Types of functions
1.scalar
2.inline table values
3.Multitable valued

 --get salery of an employee using his Empno
 create function Multiply(@a int,@b int)
 RETURNS int
 as
 begin

 return @a*@b

 End

 select dbo.Multiply (10,2000000) as yjreddyincomeperyear
--inline table valued fn
CREATE FUNCTION Getdetailsbyempno
( @EmpNo int )
RETURNS table
AS
RETURN (
SELECT Empname, salery
FROM Employeerecord
WHERE EmpNo=@EmpNo
)
select * from Getdetailsbyempno(7900)

 



User-Defined Functions



There are three Types of UDFS in Sql Server:
1. Scalar
2. Inline Table-Valued
3. Multi-statement Table-Valued
Let us go through each of these with examples:
1. Scalar User-Defined Function
A Scalar UDF can accept 0 to many input parameter and will return a single value. A Scalar user-defined
function returns one of the scalar (int, char, varchar etc) data types. Text, ntext, image and timestamp data types
are not supported. These are the type of user-defined functions that most developers are used to in other
programming languages.
Example 1: Here we are creating a Scalar UDF AddTwoNumbers which accepts two input parameters @a and
@b and returns output as the sum of the two input parameters.
CREATE FUNCTION AddTwoNumbers
(
@a int,
@b int
)
RETURNS int
AS
BEGIN
10/ 9/ 12 User Def ined Funct ion « BeginSQ L
beginsql. wor dpr ess. com/ cat egor y/ sql- ser ver / user - def ined- f unct ion/ 2/ 6
BEGIN
RETURN @a + @b
END
Once the above function is created we can use this function as below:
PRINT dbo.AddTwoNumbers(10,20)
OR
SELECT dbo.AddTwoNumbers(30,20)
Note: For Scalar UDFS we need to use Two Part Naming Convention i.e. in the above two statements we are
using dbo.AddTwoNumbers.
Whether Below statement is correct? No, because it is not using two-part naming convention. Try executing the
below statement it will error out…
PRINT AddTwoNumbers(10,20)
2. Inline Table-Valued User-Defined Function
An inline table-valued function returns a variable of data type table whose value is derived from a single SELECT
statement. Since the return value is derived from the SELECT statement, there is no BEGIN/END block needed
in the CREATE FUNCTION statement. There is also no need to specify the table variable name (or column
definitions for the table variable) because the structure of the returned value is generated from the columns that
compose the SELECT statement. Because the results are a function of the columns referenced in the SELECT,
no duplicate column names are allowed and all derived columns must have an associated alias.
Example: In this example we are creating a Inline table-valued function GetAuthorsByState which accepts
state as the input parameter and returns firstname and lastname of all the authors belonging to the input state.
USE PUBS
GO
CREATE FUNCTION GetAuthorsByState
( @state char(2) )
RETURNS table
AS
RETURN (
SELECT au_fname, au_lname
FROM Authors
WHERE state=@state
)
GO
We can use the below statement to get all the authors in the state CA.
10/ 9/ 12 User Def ined Funct ion « BeginSQ L
beginsql. wor dpr ess. com/ cat egor y/ sql- ser ver / user - def ined- f unct ion/ 3/ 6
SELECT * FROM GetAuthorsByState(‘CA’)
3. Multi-statement Table-Valued User-Defined Function
A Multi-Statement Table-Valued user-defined function returns a table. It can have one or more than one TSql
statement. Within the create function command you must define the table structure that is being returned.
After creating this type of user-defined function, we can use it in the FROM clause of a T-SQL command unlike
the behavior found when using a stored procedure which can also return record sets.
Example: In this example we are creating a Multi-Statement Table-Valued function GetAuthorsByState which
accepts state as the input parameter and returns author id and firstname of all the authors belonging to the
input state. If for the input state there are no authors then this UDF will return a record with no au_id column
value and firstname as ‘No Authors Found’.
USE PUBS
GO
CREATE FUNCTION GetAuthorsByState
( @state char(2) )
RETURNS
@AuthorsByState table (
au_id Varchar(11),
au_fname Varchar(20)
)
AS
BEGIN
INSERT INTO @AuthorsByState
SELECT au_id,
au_fname
FROM Authors
WHERE state = @state
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO @AuthorsByState
VALUES (”,’No Authors Found’)
END
RETURN
END
GO
We can use the below statements to get all the authors in the given input state:
SELECT * FROM GetAuthorsByState(‘CA’)
SELECT * FROM GetAuthorsByState(‘XY’)