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

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 "

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 Luharuka · 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 (5)