Create a Scalar Valued Function in SQL Server

Vipendra Verma

14y5.8k

Hi friends

I want to know that how to create a scalar valued function in SQL Server. Please explain it with example.

Thank you

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Aravind Govindaraj3y ago
Basically, Scalar Value means which returns a single primitive value, For eg., you want to get the age of the person so you will pass the dob of the person which returns the age of the person.

please find the example for the same

CREATE FUNCTION getPersonAge(

@dob datetime

)

RETURN INT

AS
BEGIN

//Calculate the Current Date - @dob and return the age

END;
0
Naresh Beniwal3y ago
https://www.javatpoint.com/scalar-functions-in-sql
0
Yazhini MP4y ago
To create a scalar function, use the CREATE FUNCTION statement as follows,

CREATE FUNCTION [ schema_name.]function_name (parameter_list)
RETURNS data_type AS
BEGIN
statements
RETURN value
END

In this syntax,
1.First,specify the name of the function after the CREATE FUNCTION keywords. The schema name is optional.
2.Second, specify a list of parameters surrounded by parentheses after the function name.
3.Third, specify the data type of the return value in the RETURNS statement.
4.Finally, include a RETURN statement to return a value inside the body of the function.
0
geetha geetha4y ago
Scalar Value function can returns the single value .
In SQL server, scalar value fuction may or may not have a parameter list that is optional.
The return valued type can be any type of data except the image,cursor and timestamp.
Query for creating scalar value function:
CREATE FUNCTION [schemaName.]fnName (parameterlist)
RETURNS data_type AS
BEGIN
statements
RETURN value
END
Query for alter scalar value function:
ALTER FUNCTION [schemaName.]fnName (parameterlist)
RETURNS data_type AS
BEGIN
statements
RETURN value
END
Remove scalar valued function:
DROP FUNCTION [schemaName.]fnName;
Instead of using this query , you can select the database name then click the programmabilty after that you can click functions then choose Scalar value function.
Here I attached the screenshots for your reference.

0
Pankajkumar Patel4y ago
Hi Vipendra Verma,

For how to create a scalar valued function in SQL Server. Please refer the below article with example:
- How to Create Scalar Value Function in SQL Server
  
  https://www.c-sharpcorner.com/UploadFile/898089/how-to-create-scalar-value-function-in-sql-server-2012/
Hope, this will help you!
+2
Vinitha T4y ago
Scalar Valued Function is a User-defined function that may or may not have parameter and return only a single value. It may have one or more parameters. The return value in Scalar valued function can be any data type except text, ntext, cursor and timestamp.

CREATE FUNCTION functionName(
@parameter1 datatype,
@parameter2 datatype)
RETURN datatype
AS
BEGIN
statement
RETURN statement
END;

After creation function lie at Programmability -> Functions -> Scalar-valued Functions

Created function can be called by:
SELECT functionName(value, value);
+1
MuthuMari M14y ago
Hi,

A scalar-valued function (SVF) is a user-defined function (UDF) that returns a single value. Scalar-valued functions can take arguments and return values of any scalar data type supported by SQL Server except rowversion, text, ntext, image, timestamp, table, or cursor.

pls refer this url for samples

http://www.yaldex.com/sql_server/progsqlsvr-CHP-5-SECT-1.html

thanks.

If this post is useful then mark it as "Accepted Answer"
+2
Jignesh Trivedi14y ago
hi,

A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported.
You pass in 0 to many parameters and you get a return value.
Example
CREATE FUNCTION MyValue
(@value int)
RETURNS varchar(30)
AS
BEGIN
declare @Return varchar(30)
select @return = case @value
when 1 then 'One'
when 2 then 'Two'
when 3 then 'Three'
when 4 then 'Four'
when 5 then 'Five'
else 'More than Five'
end
return @return
end
Also think about CTE.
hope this help.
0
Senthilkumar14y ago
Hi Vipendra,

Functions are similar to functions you write in any programming languages. A function is a piece of code or routine that accepts parameters and is stored as an object in SQL server.

Function always return result set from a function

One of the key difference between functions and stored procedure is that function can be called within a SELECT statement or even in WHERE clause, while a stored procedure is called by using an EXECUTE procedure statement.

Scalar Function

A scalar function returns a single data value.

CREATE FUNCTION [scheme_name.]function_name
( [ @parameter_name AS [type_schema_name.]parameter_data_type
= default ],…n]
]
)
RETURNS return_data_type
[WITH [….n] ]
AS
BEGIN

RETURN scalar_expression
END [ ; ]

Example:

Create Function dbo.fn_dollar_toEuro(@dollar money)
returns money
as
begin
declare @result money
set @result = @dollar /1.10
return @result
end

print dbo.fn_dollar_toEuro(10)

Create FUNCTION [dbo].[Get_Active_Rule_Status]
(
@ReviewName nvarchar(50),
@RuleName nvarchar(50)
)
RETURNS BIT
AS
BEGIN
RETURN (SELECT cast(apply_rule as bit) from PVH_Rules where ReviewName = @ReviewName and RuleName = @RuleName)
END
0
Satyapriya Nayak14y ago
Hi Vipendra,

Please refer the below link

http://codingresource.blogspot.in/2010/01/creating-scalar-valued-functions-in-sql.html

Thanks
0
Nitesh Kejriwal14y ago
Vipendra,

Introduced in SQL 2005, a scalar-valued function (SVF) is a function that returns a single value, such as a string, integer, or bit value. You can perform any operation in the function, but the final output will be a single value. In programming terms, it is a function that returns a base datatype(int/string/bool etc.)

For example-
```
CREATE FUNCTION GetRowsCount RETURNS int AS BEGIN    DECLARE @RowsCount int    SELECT @RowsCount = COUNT(*) AS 'Order Count' FROM SalesOrderHeader    RETURN @RowsCount  END
```
0