Scalar-Valued User Defined Function in SQL SERVER
We will look into the Scalar-Valued User Defined Function The syntax for Scalar-Valued is as follows CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [ ,...n ] ] ) RETURNS return_data_type [ WITH [ ,...n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END [ ; ] The below example shows how a function returns the phone number in US Phone format (XXX)XXX-XXXX CREATE FUNCTION USFORMATPHONE(@PhoneNumber varchar(25)) RETURNS Varchar(50) AS BEGIN DECLARE @phone varchar(50) IF(LEN(@PhoneNumber) = 10) SET @phone = '('+ SUBSTRING(@PhoneNumber,1,3) + ')' + SUBSTRING(@PhoneNumber,4,3) + '-' + SUBSTRING(@PhoneNumber,7,4) ELSE SET @phone = 'Invalid Phone Number' Return @phone END The usage of the same is as follows SELECT EMPLOYEE.DBO.USFORMATPHONE ('1234567890')