The CREATE FUNCTION statement creates a user-defined function (UDF) in T-SQL that can return a value. You can use these functions just like the system functions and you can call them in your queries just like stored procedures using the EXECUTE statement. Here is a simple example that returns the abbreviation for the name of a month, returned from passing in an ordinal value representing the number of the month in a year:
CREATE FUNCTION [dbo].[GETMONBYSHORTNAME] (@MONNUMBER VARCHAR (2)) RETURNS VARCHAR (3) AS BEGIN declare @fData varchar (2), @val1 varchar (100) , @returnval varchar (3) select @fData=@MONNUMBER SELECT @val1=CASE @fData WHEN '01' THEN 'JAN' WHEN '02' THEN 'FEB' WHEN '03' THEN 'MAR' WHEN '04' THEN 'APR' WHEN '05' THEN 'MAY' WHEN '06' THEN 'JUN' WHEN '07' THEN 'JUL' WHEN '08' THEN 'AUG' WHEN '09' THEN 'SEP' WHEN '10' THEN 'OCT' WHEN '11' THEN 'NOV' WHEN '12' THEN 'DEC' END begin select @returnval=@val1 end return @returnval end
User ALTER FUNCTION to change this function. UDFs in C# or VB that run on the CLR are more exciting. We discuss these next.