Introduction:

In this tutorial we will learn what is scalar function in SQL Server and how to return single value via scalar function with Example:

Description:

Basically a scalar UDF is a function that accepts zero or more parameter and return a single scalar value as the result.You are probably already familiar with scalar functions in mathematics, and with T-SQL’s built-in scalar function like ABS and SUBSTRING. Create function statement is used to create a Scalar-valued function.   let’s create scalar function create syntax:

Create a table

create table Student
(
id bigint identity(1,1) primary key,
Name nvarchar(150),
Mobile nvarchar(100),
address1 nvarchar(500)
)

Insert data in Student Table

insert into Student values('Rajee','999999999','allahabad')
insert
into Student values('ADITYA','888888888','allahabad')

 

Create a Scalar-valued function

CREATE FUNCTION dbo.GetName (@Id bigint=0)
RETURNS nvarchar(100)
AS
BEGIN
Declare @UserName nvarchar(100);
select @UserName=Name from student where StudentID=@id ;
return
@UserName;
END;

As we want to use function in SQL server syntax:

select dbo.GetName(1);

OUTPUT:

SQL Scalar-valued function


Thanks.

 

Comments

  1. SQL Scalar-valued function mac bhijan
    7/13/2018 10:22:00 PM

    SQL Server scalar function is a function that operates on scalar values see on mindstick.com https://www.mindstick.com/Articles/12998/what-is-scalar-function-in-sql


  2. By:priyanka patel mac bhijan
    6/1/2019 5:16:55 AM

    very useful article



Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

You may also like