Create a Scalar Valued Function in SQL Server

Question

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

Pankajkumar Patel · Answer

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!

Vinitha T · Answer

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);

MuthuMari M · Answer

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 "

Naresh Beniwal · Answer

https://www.javatpoint.com/scalar-functions-in-sql

Aravind Govindaraj · Answer

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;

Yazhini MP · Answer

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.

geetha geetha · Answer

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.

Jignesh Trivedi · Answer

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.

Senthilkumar · Answer

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

Satyapriya Nayak · Answer

Hi Vipendra, Please refer the below link http://codingresource.blogspot.in/2010/01/creating-scalar-valued-functions-in-sql.html Thanks

Nitesh Kejriwal · Answer

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

Create a Scalar Valued Function in SQL Server

Answers (11)