SQL Server - User Define Functions


Functions in SQL Server help us to perform specific tasks. It helps to increase the code re-usability.

Types of user define functions in SQL Server.
1) Scalar Function
2) Table-Valued Function


When to use the user define functions in SQL Server?
If we have requirement where some repetitive and complex code need to use more places and which is returning some type of information that time we can create one common function of SQL Server and return required value.

Second important use of function is we can use SQL Server function with in-line select, insert and update statements of SQL Server.


Note : Important thing about SQL Server function is, it must return the value.
In Scalar Function it return single value where in Table-Valued function it return the table as value.



Let's start


1) Scalar (single value) Function.

Scalar means single value, In SQL Server Scalar function always return only one single value. This is most preferred function to use with select, insert and update statements.

Syntax of Scalar Function

   CREATE FUNCTION < function name > ( < parameter 1>, < parameter 2>,... )
   RETURNS < data type > AS
   BEGIN
< function statements >
:
:
< function statements >
   
RETURN < value >
   END

   --How to call function.
   SELECT < function name > ( < parameter 1>, < parameter 2>,... )

If you observe the above function syntax
First create function with list of parameters if required .
Second and important is return statement. In second statement we declare return types function.
After that we use some statements in BEGIN - END block and return final value.


Practical example of Scalar Function.

Let's consider the below result set of student where we need to find out the percentage of each student with marks using scalar value function.


user define function practical example in sql server.

Example

   CREATE FUNCTION GetPercentage (@Math INT , @Science INT , @History INT )
   RETURNS DECIMAL(18,2) AS
   BEGIN
DECLARE @FinalPercent DECIMAL (18,2) = 0
SET @FinalPercent = CONVERT( DECIMAL (18,2), (((@Math + @Science + @History) * 100) / 300))
   
RETURN @FinalPercent
   END

Now function get created in Data Base Name -> Functions -> Scalar-Valued-Function folder -> function name that is GetPercentage. You can see in below image.


SQL Server Scalar function tree structure.

Example

   SELECT
   StudentName,
   City,
   Math, Science, History,
   dbo.GetPercentage(Math, Science, History) AS 'Percentage'

Output

real time example of scalr function in sql server


Note : Every user define scalar function always start with dbo.< function name >



2) Table-Valued Function.

This is second type of example of user define function. This function return table as value.
This is totally different from Scalar Value function because scalar function return single value where table value function return table as value.

Syntax of Scalar Function

   CREATE FUNCTION < function name > ( < parameter 1>, < parameter 2>,... )
   RETURNS @StudentMarks Table (
    Column1
    Column2 )
   BEGIN
< function statements >
:
:
< function statements >
   
RETURN
   END

   --How to call function.
   SELECT < function name > ( < parameter 1>, < parameter 2>,... )


Example

   CREATE FUNCTION GetPercentage (@Math INT , @Science INT , @History INT )
   RETURNS @StudentMarks TABLE
   (
      Math INT
      ScienceINT
      HistoryINT
   )
   BEGIN
INSERT INTO @StudentMarks
SELECT Math, Science, History FROM tblStudentMarks WHERE StudentName = @StudentName
RETURN
   END

Note : From above example we return the table variable value in variable @StudentMarks. And with RETURN statement in table value function we not need to provide the table variable.


Now function get created in Data Base Name -> Functions -> Table-Valued-Function folder -> function name that is GetPercentage. You can see in below image.


SQL Server table value function tree structure.

Example

   SELECT * FROM GetMarksDetails('Yogesh')

Output

return table in sql server function in sql server real time example.


Note : In above example we not use dbo.< function name > because this function it-self returning table as value.



How to drop user define function in SQL Server

DROP keyword we use to drop user define function in SQL Server.

Example

   DROP FUNCTION GetMarksDetails

Output

drop user define function in sql server.




 



WebNetGuru provides the developer to learn and understand the technical concepts. We try to provide solution in easy and step by step way. We continuously keep updating content and topics, so we cannot warrant full correctness of all contents.
WebNetGuru team will try to keep updating new technology tutorial.
Current Tutorials
Upcoming Tutorials
  • ASP.NET MVC
  • Angular JS
  • Mongo DB
CONTACT US
  • contact@webnetguru.com
WORK WITH US
© Copyright 2020 WebNetGuru.com