SQL Server Function

User-Defined Functions are part of a of Database. User-Defined Functions accepts parameters (not mandatory),do work , and returns a type of result.The return may be integer,decimal,string even a table.

Syntax:

CREATE FUNCTION [dbo].[You Function Name]

(

@parameter DATATYPE

..

..

)

RETURNS DATATYPE

AS

BEGIN

RETURN Result

END

Function Call Syntax :

SELECT [dbo].[You Function Name]( @parameter DATATYPE .. ..)

Here is the example of function that calculate square of a number

 

CREATE FUNCTION dbo.fn_GetSquare

(

       @param1 int

)

RETURNS INT

AS

BEGIN

        RETURN @param1 * @param1

END

Here is the result.

SELECT dbo.fn_GetSquare(12) /* Result :144*/

SELECT dbo.fn_GetSquare(14) /* Result :196*/

SELECT dbo.fn_GetSquare(15) /* Result :225*/

SELECT dbo.fn_GetSquare(16) /* Result :256*/

Some Point on function
1)You can not use print statement in the function.

2)Function can be called from Stored Procedure, but Stored Procedure can not 

be called from functions.

3)You can not use temporary table in function.

4)Function can be called from Functions.

5)A function can return table.

Here , we have created two table and inserted data to a table for example purpose.

CREATE TABLE student

(

      id INT NOT NULL,

      name VARCHAR (500) NOT NULL,

      age INT NOT NULL,

      adress VARCHAR (500) NOT NULL,

      date_of_birth DATETIME NOT NULL,

      height DECIMAL(10,2) NOT NULL

)

INSERT INTO student(id,name,age,adress,date_of_birth,height)

SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

UNION

SELECT 1,'John2',13,'35,student street','02/08/2018',11

UNION

SELECT 1,'John3',14,'36,student street','03/08/2018',11.5

UNION

SELECT 1,'John4',15,'37,student street','04/08/2018',12

UNION

SELECT 1,'John5',16,'38,student street','05/08/2018',12.5

UNION

SELECT 1,'John6',17,'39,student street','06/08/2018',13.5

UNION

SELECT 1,'John7',18,'40,student street','07/08/2018',14

UNION

SELECT 1,'John8',19,'41,student street','08/08/2018',14.5

UNION

SELECT 1,'John9',20,'42,student street','09/08/2018',15

UNION

SELECT 1,'John10',21,'43,student street','10/08/2018',15.5

UNION

SELECT 1,'John11',22,'44,student street','11/08/2018',16

Here is the example of function that return a table.

CREATE FUNCTION dbo.fn_GetStudentData()

     RETURNS @t TABLE

    (

           id INT NULL,

           name NVARCHAR(500) NOT NULL

    )

AS

BEGIN

     DECLARE @t1 TABLE

         (

           id INT ,

           name NVARCHAR(500)

          )

      INSERT INTO @t1 (id,name)

      SELECT id,name FROM student

      INSERT INTO @t(id,name)

      SELECT id,name FROM @t1

RETURN

END

Now we are calling the function.

SELECT * FROM dbo.fn_GetStudentData()

 

Output:
id   name
1    John10
1    John11
1    John2
1    John3
1    John4
1    John5
1    John6
1    John7
1    John8
1    John9
5    john25