Altering User-Defined Functions
We can modify the user defined functions by using the Alter command.
The Syntax as shown below
Scalar-valued Functions
ALTER FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS return_data_type [ WITH [ ,...n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END [ ; ]
Table-valued Functions
ALTER FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE [ WITH [ ,...n ] ] [ AS ] BEGIN function_body RETURN END [ ; ]
The below shows how a function has been modified to change the message
ALTER 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 = 'Phone Number is invalid'
Return @phone
END
The Syntax as shown below
Scalar-valued Functions
ALTER FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS return_data_type [ WITH
Table-valued Functions
ALTER FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE
The below shows how a function has been modified to change the message
ALTER 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 = 'Phone Number is invalid'
Return @phone
END
Comments