Understanding about Tabular valued and scalar valued function in SQL

Опубликовано: 04 Октябрь 2024
на канале: rejawebs

Hello everyone,
Welcome to tutorial on User Define Function in SQL
In this course we will talk about the use of Table Valued, scalar valued Function in SQL

Table Valued Function:

A table-valued function is a user-defined function that returns data of a table type.

Table valued function that allows us to encapsulate logic and to return tabular data.

Table Valued Function Syntax:

CREATE FUNCTION Function_Name

(

Parameter1 DataType,

Parameter1 DataType,

..

ParameterN DataType

)

RETURNS Table_To_Be_Return TABLE

(

Column1 DataType,

Column2 DataType,

...

ColumnN DataType

)

AS

BEGIN

--Here you logic

RETURN;

END

So let's take a look in query window.

SQL Query:

CREATE FUNCTION [dbo].[fn_GetEmployeeById]

(

@EmployeeId INT

)

RETURNS @EmployeeListing TABLE

(

EmployeeId INT,

EmployeeName NVARCHAR(100),

EmployeeGrosssSalary DECIMAL(18,2)

)

AS

BEGIN

--Here you logic

INSERT INTO @EmployeeListing

SELECT

Id

, Fistname

, GrossSalary

FROM EmployeeInfo WHERE Id=@EmployeeId

RETURN;
@EmployeeListing WILL BE RETURN.

END

GO

SELECT *FROM [dbo].[fn_GetEmployeeById](1)

Scalar Valued Function:

Scalar valued functions that helps us to encapsulate logic and to return scalar data.

Scalar Valued Function Syntax:
CREATE FUNCTION Function_Name
(
Parameter1 DataType,

Parameter1 DataType,
........
ParameterN DataType
)

RETURNS DataType
AS
BEGIN
RETURN '';
END

So let's take a look in query window.

SQL Query:
CREATE FUNCTION [dbo].[fn_GetEmployeeGrossSalaryById]
(
@EmployeeId INT
)
RETURNS DECIMAL(18,2)
AS
BEGIN
DECLARE
@EmployeeGrossSalary DECIMAL(18,2)=0;
SELECT TOP 1
@EmployeeGrossSalary=GrossSalary
FROM EmployeeInfo

RETURN @EmployeeGrossSalary;
END

GO
SELECT [dbo].[fn_GetEmployeeGrossSalaryById](1)

GO
SELECT *FROM EmployeeInfo
WHERE
GrossSalary=[dbo].[fn_GetEmployeeGrossSalaryById](1)

Keep Watching the next tutorial.
Thank you.