Create Functions in SQL

Functions provide a good way to group some set of statements under a particular valid Function -Name that can used in various portions in Program . Functions allows us to use particular set of statements that will be used a number of times in different portions of program by single name. A particular set of statements used to perform specific function and
we know these statements will be repeatedly used in various parts of program then instead of increasing the Redundancy of code or repeated code in the program  we have placed all this code single time in a function we always call that function whenever we requires that code

SYNTAX :- Syntax To Create A function in SQL :-

Create Function Function-Name ( @Parameter-Name  Parameter-value=type )
returns  Return-Type
as
begin
               Set
                      Of
                          Statements
end

* Here, Create and Function  are keywords that are reserved to create functions in Structured Query Language.
* Function-Name could be any legal group of characters . It must consist of single word . If we want to use more than one word then they are joined using Underscore sign.
* Returns statement tells the type of values the function will return to calling procedure
*  as is also a keyword used to define which work function have to perform
* The work that the function has to perform  is placed as the set of statements under Begin and End Keywords.



CALL FUNCTION IN SQL  SYNTAX :-

Select dbo.function-Name( parameter list )
                OR
select function-Name( parameter list )

EXAMPLE :-

QUERY 1 :- Create a function to know your grade in SQL by Entering numbers

create function getgrade ( @number int )
returns varchar (10)
as
begin
if ( @number >250 and @number <=350 )
return 'A grade'
else if ( @number <=250 and @number>200 )
return 'B grade'
else if ( @number >=150 and @number <=200 )
return 'C grade'

return 'D grade'           

end

select dbo.getgrade(200)as 'Your grades'

DESCRIPTION :-

* Here, Create and Function  are keywords that are reserved to create functions in Structured Query Language.
* getgrade is the name of function . It consist of one Parameter of integer Type
                     
                                         @number int 

* Here @number int is the parameter of getgrade function
* In SQL (Structured Query Language) @ is used for declaring the temporary variable and int tells the type of variable is it that defines the type of data it can store.

                                        returns varchar (10)

* Returns statement tells the type of values the function getgrade will return to calling procedure . Here, Varchar followed by returns so getgrade function will return a varchar value that can have maximum length of 10 characters

*  as is also a keyword used to define which work function have to perform

* The work that the function has to perform  is placed as the set of statements under Begin and End Keywords.

                               select dbo.getgrade(200) as 'Your grades'



* When we Execute this statement  it will first call getgrade function . As getgrade function has one parameter @number and we are also calling the function getgrade with a integer value '200' @number will be assigned with '200' value 

*  Now control goes through if -- Else if statements and return the value according to the value assigned to function parameter @number

* @number=200 so it returns C grade


OUTPUT :-

QUERY 2 :- Create a function to Display Addition of Two Numbers without using third variable

create function ADDITION(@number1 int, @number2 int)
returns int
as
begin
return (@number1 + @number2)
end

select dbo.ADDITION(5,6) as RESULT

OUTPUT :-

QUERY 3 :- Create a function to Display Addition of Two Numbers using third variable

create function ADDITION1(@number1 int, @number2 int)
returns int
as
begin
declare @c int
set @c = @number1 + @number2
return @c
end

select dbo.ADDITION(5,6) as RESULT

OUTPUT   :-

Share this

heemanshu bhalla