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