User Defined Functions in T-SQL

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.