SQL Server/T-SQL Tutorial/Procedure Function/Create function

A statement that creates a scalar-valued function

   <source lang="sql">

26> create table Bankers( 27> BankerID Integer, 28> BankerName VARCHAR(20), 29> BankerContactLName VARCHAR(20), 30> BankerContactFName VARCHAR(20), 31> BankerCity VARCHAR(20), 32> BankerState VARCHAR(20), 33> BankerZipCode VARCHAR(20), 34> BankerPhone VARCHAR(20) 35> ) 36> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> CREATE FUNCTION fnBankerID 4> (@BankerName varchar(50)) 5> RETURNS int 6> BEGIN 7> RETURN (SELECT BankerID FROM Bankers WHERE BankerName = @BankerName) 8> END 9> GO 1> 2> drop FUNCTION fnBankerID; 3> GO 1> drop table Bankers; 2> GO 1></source>

A statement that creates a simple table-valued function

   <source lang="sql">

17> create table Billings ( 18> BankerID INTEGER, 19> BillingNumber INTEGER, 20> BillingDate datetime, 21> BillingTotal INTEGER, 22> TermsID INTEGER, 23> BillingDueDate datetime , 24> PaymentTotal INTEGER, 25> CreditTotal INTEGER 26> 27> ); 28> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> create table Bankers( 4> BankerID Integer, 5> BankerName VARCHAR(20), 6> BankerContactLName VARCHAR(20), 7> BankerContactFName VARCHAR(20), 8> BankerCity VARCHAR(20), 9> BankerState VARCHAR(20), 10> BankerZipCode VARCHAR(20), 11> BankerPhone VARCHAR(20) 12> ) 13> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> CREATE FUNCTION fnTopBankersDue 4> (@CutOff money = 0) 5> RETURNS table 6> RETURN 7> (SELECT BankerName, SUM(BillingTotal) AS TotalDue 8> FROM Bankers JOIN Billings ON Bankers.BankerID = Billings.BankerID 9> WHERE BillingTotal - CreditTotal - PaymentTotal > 0 10> GROUP BY BankerName 11> HAVING SUM(BillingTotal) >= @CutOff) 12> GO 1> 2> 3> 4> --A SELECT statement that invokes the function 5> 6> SELECT * FROM dbo.fnTopBankersDue(5000) 7> 8> drop FUNCTION fnTopBankersDue; 9> GO BankerName TotalDue

---

-----------

(0 rows affected) 1> 2> 3> drop table Bankers; 4> GO 1> 2> drop table Billings; 3> GO</source>

Creating a User-Defined Function

   <source lang="sql">

5> CREATE Function dbo.fn_LastOfMonth(@TheDate DateTime) 6> Returns DateTime 7> AS 8> BEGIN 9> DECLARE @FirstOfMonth DateTime 10> DECLARE @DaysInMonth Int 11> DECLARE @RetDate DateTime 12> SET @FirstOfMonth = DATEADD(mm, DATEDIFF(mm,0,@TheDate), 0) 13> SET @DaysInMonth = DATEDIFF(d, @FirstOfMonth, DATEADD(m, 1, @FirstOfMonth)) 14> RETURN DATEADD(d, @DaysInMonth - 1, @FirstOfMonth) 15> END 16> GO 1> 2> drop function dbo.fn_LastOfMonth; 3> GO 1></source>

Scalar functions are functions that return a single value.

   <source lang="sql">

Scalar functions can accept zero or more input parameters. Syntax for Creating Scalar Functions CREATE FUNCTION [ owner_name. ] function_name

   ( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS scalar_return_data_type [ WITH { ENCRYPTION | SCHEMABINDING } [,...n] ] [ AS ] BEGIN

   function_body
   RETURN scalar_expression

END</source>

Syntax for Creating Inline Table-Valued Functions

   <source lang="sql">

CREATE FUNCTION [ owner_name. ] function_name

   ( [ { @parameter_name scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS TABLE [ WITH { ENCRYPTION | SCHEMABINDING } [ ,...n ] ] [ AS ] RETURN [ ( ] select-stmt [ ) ]</source>

Syntax for Creating Multistatement Table-Valued Functions

   <source lang="sql">

CREATE FUNCTION [ owner_name. ] function_name

     ( [ { @parameter_name scalar_parameter_data_type
          [ = default ] } [ ,...n ] ] )

RETURNS @return_variable TABLE

 ( { column_definition | table_constraint } [ ,...n ] )

[ WITH { ENCRYPTION | SCHEMABINDING } [ ,...n ] ] [ AS ] BEGIN

   function_body
   RETURN

END</source>