SQL Server/T-SQL Tutorial/Procedure Function/function
Содержание
- 1 A SELECT statement that uses the function in a join operation
- 2 Call two user-defined functions in a select statement
- 3 Call user-defined function in where clause
- 4 Create a scalar-valued function that returns the total Billing amount due
- 5 Function WITH SCHEMABINDING
- 6 Get the 3 employees with the most RegionPlace
- 7 Invoke the scalar-valued function
- 8 Pass a declared variable to a function
- 9 Query a table returned from a function
- 10 Return TOP 100 PERCENT WITH TIES from a function
- 11 The syntax for altering a multi-statement table-valued function
- 12 The syntax for altering a simple table-valued function
- 13 The syntax for creating a multi-statement table-valued function
- 14 The syntax for creating a scalar-valued function
- 15 The syntax for creating a simple table-valued function
- 16 The syntax of the ALTER FUNCTION statement for a scalar valued function
- 17 Use function as a view
- 18 Use function to wrap a long sql statement
A SELECT statement that uses the function in a join operation
4>
5>
6> create table Billings (
7> BankerID INTEGER,
8> BillingNumber INTEGER,
9> BillingDate datetime,
10> BillingTotal INTEGER,
11> TermsID INTEGER,
12> BillingDueDate datetime ,
13> PaymentTotal INTEGER,
14> CreditTotal INTEGER
15>
16> );
17> 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>
9> SELECT Bankers.BankerName, BankerCity, TotalDue
10> FROM Bankers JOIN dbo.fnTopBankersDue(DEFAULT) AS TopBankers
11> ON Bankers.BankerName = TopBankers.BankerName
12> GO
BankerName TotalDue
-------------------- -----------
(0 rows affected)
BankerName BankerCity TotalDue
-------------------- -------------------- -----------
(0 rows affected)
1>
2> drop FUNCTION fnTopBankersDue;
3> GO
1>
2>
3> drop table Bankers;
4> GO
Call two user-defined functions in a select statement
6> CREATE TABLE titles(
7> title_id varchar(20),
8> title varchar(80) NOT NULL,
9> type char(12) NOT NULL,
10> pub_id char(4) NULL,
11> price money NULL,
12> advance money NULL,
13> royalty int NULL,
14> ytd_sales int NULL,
15> notes varchar(200) NULL,
16> pubdate datetime NOT NULL
17> )
18> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> CREATE FUNCTION dbo.AveragePrice()
2> RETURNS money
3> WITH SCHEMABINDING
4> AS
5> BEGIN
6> RETURN (SELECT AVG(Price) FROM dbo.Titles)
7> END
8> GO
1>
2> CREATE FUNCTION dbo.PriceDifference(@Price money)
3> RETURNS money
4> AS
5> BEGIN
6> RETURN @Price - dbo.AveragePrice()
7> END
8> GO
1> SELECT Title,
2> Price,
3> dbo.AveragePrice() AS Average,
4> dbo.PriceDifference(Price) AS Difference
5> FROM Titles
6> WHERE Type="popular_comp"
7> GO
Title Price Average Difference
-------------------------------------------------------------------------------- --------------------- --------------------- ---------------------
Secrets 20.0000 15.8562 4.1438
Secrets 20.0000 15.8562 4.1438
Secrets 20.0000 15.8562 4.1438
Secrets 20.0000 15.8562 4.1438
Secrets 20.0000 15.8562 4.1438
Secrets 20.0000 15.8562 4.1438
Secrets 20.0000 15.8562 4.1438
(7 rows affected)
1>
Call user-defined function in where clause
4> CREATE TABLE titles(
5> title_id varchar(20),
6> title varchar(80) NOT NULL,
7> type char(12) NOT NULL,
8> pub_id char(4) NULL,
9> price money NULL,
10> advance money NULL,
11> royalty int NULL,
12> ytd_sales int NULL,
13> notes varchar(200) NULL,
14> pubdate datetime NOT NULL
15> )
16> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
3> CREATE FUNCTION AveragePrice(@booktype varchar(12))
4> RETURNS money
5> AS
6> BEGIN
7> DECLARE @avg money
8> SELECT @avg = avg(price)
9> FROM titles
10> WHERE type = @booktype
11>
12> RETURN @avg
13> END
14> GO
1>
2> SELECT title_id, price
3> FROM titles
4> WHERE price > dbo.AveragePrice("business")
5> AND type = "business"
6> GO
title_id price
-------------------- ---------------------
2 19.9900
1>
2> DECLARE @avg money
3> EXEC @avg = dbo.AveragePrice "business"
4> SELECT @avg
5> GO
---------------------
15.9700
1>
2>
3> drop FUNCTION AveragePrice;
4> GO
1>
2> drop table titles;
3> GO
1>
Create a scalar-valued function that returns the total Billing amount due
6> create table Billings (
7> BankerID INTEGER,
8> BillingNumber INTEGER,
9> BillingDate datetime,
10> BillingTotal INTEGER,
11> TermsID INTEGER,
12> BillingDueDate datetime ,
13> PaymentTotal INTEGER,
14> CreditTotal INTEGER
15>
16> );
17> 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> CREATE FUNCTION fnBalanceDue()
3> RETURNS money
4> BEGIN
5> RETURN (SELECT SUM(BillingTotal - PaymentTotal - CreditTotal)
6> FROM Billings
7> WHERE BillingTotal - PaymentTotal - CreditTotal > 0)
8> END
9> GO
1>
2>
3>
4> --A script that invokes the function
5> PRINT "Current outstanding balance is: $" +
6> CONVERT(varchar,dbo.fnBalanceDue(),1)
7>
8>
9>
10> drop FUNCTION fnBalanceDue;
11> GO
1>
2>
3> drop table Billings;
4> GO
1>
Function WITH SCHEMABINDING
4> CREATE TABLE titles(
5> title_id varchar(20),
6> title varchar(80) NOT NULL,
7> type char(12) NOT NULL,
8> pub_id char(4) NULL,
9> price money NULL,
10> advance money NULL,
11> royalty int NULL,
12> ytd_sales int NULL,
13> notes varchar(200) NULL,
14> pubdate datetime NOT NULL
15> )
16> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE TABLE sales(
3> stor_id char(4) NOT NULL,
4> ord_num varchar(20) NOT NULL,
5> ord_date datetime NOT NULL,
6> qty smallint NOT NULL,
7> payterms varchar(12) NOT NULL,
8> title_id varchar(80)
9> )
10> GO
1> insert sales values("1", "QA7442.3", "09/03/94", 75, "ON Billing","1")
2> insert sales values("2", "D4482", "09/04/94", 10, "Net 60", "1")
3> insert sales values("3", "N914008", "09/04/94", 20, "Net 30", "2")
4> insert sales values("4", "N914014", "09/04/94", 25, "Net 30", "3")
5> insert sales values("5", "423LL922", "09/04/94", 15, "ON Billing","3")
6> insert sales values("6", "423LL930", "09/04/94", 10, "ON Billing","2")
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> Create FUNCTION SalesByStore(@storid varchar(30))
4> RETURNS TABLE
5> AS
6> RETURN (SELECT title, qty
7> FROM dbo.sales s, dbo.titles t
8> WHERE s.stor_id = @storid AND t.title_id = s.title_id)
9> GO
1>
2>
3> ALTER FUNCTION SalesByStore(@storid varchar(30))
4> RETURNS TABLE
5> WITH SCHEMABINDING
6> AS
7> RETURN (SELECT title, qty
8> FROM dbo.sales s, dbo.titles t
9> WHERE s.stor_id = @storid AND t.title_id = s.title_id)
10> GO
1>
2>
3> SELECT obj_name = SUBSTRING(OBJECT_NAME(d.id), 1, 20),
4> dep_obj = SUBSTRING(OBJECT_NAME(d.depid), 1, 20),
5> col_name = SUBSTRING(name, 1, 15),
6> IsSchemaBound = CASE deptype
7> WHEN 1 THEN "Schema Bound"
8> ELSE "Free"
9> END
10> FROM
11> sysdepends d
12> JOIN syscolumns c ON d.depid = c.id
13> AND d.depnumber = c.colid
14> WHERE object_name(d.id) LIKE "SalesByStore%"
15> GO
obj_name dep_obj col_name IsSchemaBound
-------------------- -------------------- --------------- -------------
SalesByStore titles title_id Schema Bound
SalesByStore titles title Schema Bound
SalesByStore sales stor_id Schema Bound
SalesByStore sales qty Schema Bound
SalesByStore sales title_id Schema Bound
(5 rows affected)
1>
2>
3>
4> SELECT routine_definition
5> FROM INFORMATION_SCHEMA.routines
6> WHERE routine_name = "SalesByStore"
7> GO
routine_definition
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION SalesByStore(@storid varchar(30))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (SELECT title, qty
FROM dbo.sales s, dbo.titles t
WHERE s.stor_id = @storid AND t.title_id = s.title_id)
(1 rows affected)
1>
2>
3>
4> drop FUNCTION SalesByStore;
5> GO
1> drop table sales;
2> drop table titles;
3> GO
1>
Get the 3 employees with the most RegionPlace
2>
3>
4> CREATE TABLE EmployeeRegion
5> (EmployeeID int NOT NULL,
6> RegionID nvarchar (20) NOT NULL
7> )
8> GO
1>
2> Insert Into EmployeeRegion Values (1,"06897")
3> Insert Into EmployeeRegion Values (1,"19713")
4> Insert Into EmployeeRegion Values (9,"48084")
5> Insert Into EmployeeRegion Values (9,"48304")
6> Insert Into EmployeeRegion Values (9,"55113")
7> Insert Into EmployeeRegion Values (9,"55439")
8> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE Employees (
4> EmployeeID int NOT NULL ,
5> LastName nvarchar (20) NOT NULL ,
6> FirstName nvarchar (10) NOT NULL ,
7> Title nvarchar (30) NULL ,
8> TitleOfCourtesy nvarchar (25) NULL ,
9> BirthDate datetime NULL ,
10> HireDate datetime NULL ,
11> Address nvarchar (60) NULL ,
12> City nvarchar (15) NULL ,
13> Region nvarchar (15) NULL ,
14> PostalCode nvarchar (10) NULL ,
15> Country nvarchar (15) NULL ,
16> HomePhone nvarchar (24) NULL ,
17> Extension nvarchar (4) NULL ,
18> Photo image NULL ,
19> Notes ntext NULL ,
20> ReportsTo int NULL ,
21> PhotoPath nvarchar (255) NULL
22>
23> )
24> GO
1>
2>
3> CREATE FUNCTION dbo.udf_EmpTerritoryCOUNT (
4> @EmployeeID int
5> ) RETURNS INT
6> AS BEGIN
7> DECLARE @RegionPlace int
8> SELECT @RegionPlace = count(*)
9> FROM EmployeeRegion
10> WHERE EmployeeID = @EmployeeID
11>
12> RETURN @RegionPlace
13> END
14> GO
1>
2>
3>
4> SELECT TOP 3 LastName, FirstName
5> , dbo.udf_EmpTerritoryCOUNT(EmployeeID) as RegionPlace
6> FROM Employees
7> WHERE dbo.udf_EmpTerritoryCOUNT(EmployeeID) > 3
8> ORDER BY dbo.udf_EmpTerritoryCOUNT(EmployeeID) desc
9> GO
LastName FirstName RegionPlace
-------------------- ---------- -----------
(0 rows affected)
1>
2>
3> drop FUNCTION dbo.udf_EmpTerritoryCOUNT;
4> GO
1>
2>
3> drop table EmployeeRegion;
4> GO
1>
2> drop table Employees;
3> GO
Invoke the scalar-valued function
9> create table Bankers(
10> BankerID Integer,
11> BankerName VARCHAR(20),
12> BankerContactLName VARCHAR(20),
13> BankerContactFName VARCHAR(20),
14> BankerCity VARCHAR(20),
15> BankerState VARCHAR(20),
16> BankerZipCode VARCHAR(20),
17> BankerPhone VARCHAR(20)
18> )
19> 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 table Billings (
4> BankerID INTEGER,
5> BillingNumber INTEGER,
6> BillingDate datetime,
7> BillingTotal INTEGER,
8> TermsID INTEGER,
9> BillingDueDate datetime ,
10> PaymentTotal INTEGER,
11> CreditTotal INTEGER
12>
13> );
14> 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 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>
3> SELECT BillingDate, BillingTotal
4> FROM Billings
5> WHERE BankerID = dbo.fnBankerID("IBM")
6> GO
BillingDate BillingTotal
----------------------- ------------
(0 rows affected)
1>
2>
3> drop FUNCTION fnBankerID;
4> GO
1> drop table Bankers;
2> GO
1> drop table Billings;
2> GO
Pass a declared variable to a function
2>
3> CREATE TABLE Employees (
4> EmployeeID int NOT NULL ,
5> LastName nvarchar (20) NOT NULL ,
6> FirstName nvarchar (10) NOT NULL ,
7> Title nvarchar (30) NULL ,
8> TitleOfCourtesy nvarchar (25) NULL ,
9> BirthDate datetime NULL ,
10> HireDate datetime NULL ,
11> Address nvarchar (60) NULL ,
12> City nvarchar (15) NULL ,
13> Region nvarchar (15) NULL ,
14> PostalCode nvarchar (10) NULL ,
15> Country nvarchar (15) NULL ,
16> HomePhone nvarchar (24) NULL ,
17> Extension nvarchar (4) NULL ,
18> Photo image NULL ,
19> Notes ntext NULL ,
20> ReportsTo int NULL ,
21> PhotoPath nvarchar (255) NULL
22>
23> )
24> GO
1>
2>
3>
4> CREATE TABLE RegionPlace
5> (RegionID nvarchar (20) NOT NULL ,
6> TerritoryDescription nchar (50) NOT NULL ,
7> RegionID int NOT NULL
8> )
9> GO
1>
2> Insert Into RegionPlace Values ("01581","Westboro",1)
3> Insert Into RegionPlace Values ("01730","Bedford",1)
4> Insert Into RegionPlace Values ("01833","Georgetow",1)
5> Insert Into RegionPlace Values ("95060","Santa Cruz",2)
6> Insert Into RegionPlace Values ("98004","Bellevue",2)
7> Insert Into RegionPlace Values ("98052","Redmond",2)
8> Insert Into RegionPlace Values ("98104","Seattle",2)
9> Go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> CREATE TABLE EmployeeRegion
2> (EmployeeID int NOT NULL,
3> RegionID nvarchar (20) NOT NULL
4> )
5> GO
1>
2> Insert Into EmployeeRegion Values (1,"06897")
3> Insert Into EmployeeRegion Values (1,"19713")
4> Insert Into EmployeeRegion Values (9,"48084")
5> Insert Into EmployeeRegion Values (9,"48304")
6> Insert Into EmployeeRegion Values (9,"55113")
7> Insert Into EmployeeRegion Values (9,"55439")
8> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE FUNCTION dbo.udf_EmpRegionPlaceTAB (
3> @EmployeeID int
4> ) RETURNS TABLE
5> AS RETURN
6> SELECT TOP 100 PERCENT WITH TIES
7> et.RegionID
8> , t.TerritoryDescription as [Territory]
9> , t.RegionID
10> FROM EmployeeRegion et
11> LEFT OUTER JOIN RegionPlace t
12> ON et.RegionID = t.RegionID
13> WHERE et.EmployeeID = @EmployeeID
14> ORDER BY t.TerritoryDescription
15>
16> GO
1>
2> DECLARE @EmpID int
3> SELECT @EmpID = EmployeeID
4> FROM Employees
5> WHERE FirstName = "Andrew" and LastName = "Fuller"
6>
7> SELECT * FROM udf_EmpRegionPlaceTAB(@EmpID)
8> GO
RegionID Territory RegionID
-------------------- -------------------------------------------------- -----------
(0 rows affected)
1>
2>
3>
4> drop FUNCTION dbo.udf_EmpRegionPlaceTAB;
5> GO
1> drop table EmployeeRegion;
2> drop table RegionPlace;
3> drop table Employees;
4> GO
1>
Query a table returned from a function
2>
2>
3> CREATE TABLE authors(
4> au_id varchar(11),
5> au_lname varchar(40) NOT NULL,
6> au_fname varchar(20) NOT NULL,
7> phone char(12) NOT NULL DEFAULT ("UNKNOWN"),
8> address varchar(40) NULL,
9> city varchar(20) NULL,
10> state char(2) NULL,
11> zip char(5) NULL,
12> contract bit NOT NULL
13> )
14> GO
1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1)
2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1)
3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1)
4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1)
5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1)
6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1)
7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1)
8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0)
9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0)
10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1)
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE FUNCTION dbo.fnAuthorList()
4> RETURNS TABLE
5> AS
6> RETURN (SELECT au_id,
7> au_lname + ", " + au_fname AS au_name,
8> address AS address1,
9> city + ", " + state + " " + zip AS address2
10> FROM authors)
11> GO
1>
2> SELECT * FROM dbo.fnAuthorList()
3> GO
au_id au_name address1 address2
----------- -------------------------------------------------------------- ---------------------------------------- ------------------------------
1 Joe, Abra 6 St. Berkeley, CA 11111
2 Jack, Majo 3 St. Oakland, CA 22222
3 Pink, Cherry 5 Ln. Vancouver, BC 33333
4 Blue, Albert 7 Av. Vancouver, BC 44444
5 Red, Anne 6 Av. Regina, SK 55555
6 Black, Michel 3 Pl. Regina, SK 66666
7 White, Sylvia 1 Pl. Rockville, MD 77777
8 Yellow, Heather 3 Pu Vacaville, CA 88888
9 Gold, Dep 5 Av. Oakland, CA 99999
10 Siler, Dean 4 Av. Oakland, CA 00000
(10 rows affected)
1>
2> drop table authors;
3> GO
1> drop FUNCTION dbo.fnAuthorList;
2> GO
Return TOP 100 PERCENT WITH TIES from a function
3> CREATE TABLE RegionPlace
4> (RegionID nvarchar (20) NOT NULL ,
5> TerritoryDescription nchar (50) NOT NULL ,
6> RegionID int NOT NULL
7> )
8> GO
1>
2> Insert Into RegionPlace Values ("01581","Westboro",1)
3> Insert Into RegionPlace Values ("01730","Bedford",1)
4> Insert Into RegionPlace Values ("01833","Georgetow",1)
5> Insert Into RegionPlace Values ("95060","Santa Cruz",2)
6> Insert Into RegionPlace Values ("98004","Bellevue",2)
7> Insert Into RegionPlace Values ("98052","Redmond",2)
8> Insert Into RegionPlace Values ("98104","Seattle",2)
9> Go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> CREATE TABLE EmployeeRegion
2> (EmployeeID int NOT NULL,
3> RegionID nvarchar (20) NOT NULL
4> )
5> GO
1>
2> Insert Into EmployeeRegion Values (1,"06897")
3> Insert Into EmployeeRegion Values (1,"19713")
4> Insert Into EmployeeRegion Values (9,"48084")
5> Insert Into EmployeeRegion Values (9,"48304")
6> Insert Into EmployeeRegion Values (9,"55113")
7> Insert Into EmployeeRegion Values (9,"55439")
8> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE FUNCTION dbo.udf_EmpRegionPlaceTAB (
3> @EmployeeID int
4> ) RETURNS TABLE
5> AS RETURN
6> SELECT TOP 100 PERCENT WITH TIES
7> et.RegionID
8> , t.TerritoryDescription as [Territory]
9> , t.RegionID
10> FROM EmployeeRegion et
11> LEFT OUTER JOIN RegionPlace t
12> ON et.RegionID = t.RegionID
13> WHERE et.EmployeeID = @EmployeeID
14> ORDER BY t.TerritoryDescription
15>
16> GO
1>
2> drop FUNCTION dbo.udf_EmpRegionPlaceTAB;
3> GO
1> drop table EmployeeRegion;
2> drop table RegionPlace;
3> GO
The syntax for altering a multi-statement table-valued function
ALTER FUNCTION [owner_name.]function_name
([@parameter_name data_type [= default]] [, ...])
RETURNS @return_variable TABLE
(column_name_1 data_type [column_attributes]
[, column_name_2 data_type [column_attributes]]...)
[WITH {ENCRYPTION|SCHEMABINDING|ENCRYPTION,SCHEMABINDING}]
BEGIN
sql_statements
RETURN
END
The syntax for altering a simple table-valued function
ALTER FUNCTION [owner_name.]function_name
([@parameter_name data_type [= default]] [, ...])
RETURNS TABLE
[WITH {ENCRYPTION|SCHEMABINDING|ENCRYPTION,SCHEMABINDING}]
RETURN [(] select_statement [)]
The syntax for creating a multi-statement table-valued function
CREATE FUNCTION [owner_name.]function_name
([@parameter_name data_type [= default]] [, ...])
RETURNS @return_variable TABLE
(column_name_1 data_type [column_attributes]
[, column_name_2 data_type [column_attributes]]...)
[WITH {ENCRYPTION|SCHEMABINDING|ENCRYPTION,SCHEMABINDING}]
[AS]
BEGIN
sql_statements
RETURN
END
The syntax for creating a scalar-valued function
CREATE FUNCTION [owner_name.]function_name
([@parameter_name data_type [= default]] [, ...])
RETURNS data_type
[WITH {ENCRYPTION|SCHEMABINDING|ENCRYPTION,SCHEMABINDING}]
[AS]
BEGIN
[sql_statements]
RETURN scalar_expression
END
The syntax for creating a simple table-valued function
CREATE FUNCTION [owner_name.]function_name
([@parameter_name data_type [= default]] [, ...])
RETURNS TABLE
[WITH {ENCRYPTION|SCHEMABINDING|ENCRYPTION,SCHEMABINDING}]
[AS]
RETURN [(] select_statement [)]
The syntax of the ALTER FUNCTION statement for a scalar valued function
ALTER FUNCTION [owner_name.]function_name
([@parameter_name data_type [= default]] [, ...])
RETURNS data_type
[WITH {ENCRYPTION|SCHEMABINDING|ENCRYPTION,SCHEMABINDING}]
BEGIN
[sql_statements]
RETURN scalar_expression
END
Use function as a view
3> CREATE TABLE Products (
4> ProductID int NOT NULL ,
5> ProductName nvarchar (40) NOT NULL ,
6> SupplierID int NULL ,
7> CategoryID int NULL ,
8> QuantityPerUnit nvarchar (20) NULL ,
9> UnitPrice money NULL,
10> UnitsInStock smallint NULL,
11> UnitsOnOrder smallint NULL,
12> ReorderLevel smallint NULL,
13> Discontinued bit NOT NULL
14> )
15> GO
1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6)
2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7)
3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0)
4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0)
5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0)
6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0)
7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0)
8> go
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE PROC spMarkupTest
3> @MarkupAsPercent money
4> AS
5> DECLARE @Multiplier money
6> SELECT @Multiplier = @MarkupAsPercent / 100 + 1
7> SELECT TOP 10 ProductId, ProductName, UnitPrice,
8> UnitPrice * @Multiplier AS "Marked Up Price", "New Price" =
9> CASE WHEN FLOOR(UnitPrice * @Multiplier + .24)
10> > FLOOR(UnitPrice * @Multiplier)
11> THEN FLOOR(UnitPrice * @Multiplier) + .95
12> WHEN FLOOR(UnitPrice * @Multiplier + .5) >
13> FLOOR(UnitPrice * @Multiplier)
14> THEN FLOOR(UnitPrice * @Multiplier) + .75
15> ELSE FLOOR(UnitPrice * @Multiplier) + .49
16> END
17> FROM Products
18> ORDER BY ProductID DESC
19>
20> GO
1>
2> EXEC spMarkupTest 10
3>
4> drop PROC spMarkupTest;
5>
6>
7> drop table Products;
8> GO
ProductId ProductName UnitPrice Marked Up Price New Price
----------- ---------------------------------------- --------------------- --------------------- ----------------------
7 O 13.0000 14.3000 14.4900
6 L 18.0000 19.8000 19.9500
5 R 1.2300 1.3530 1.4900
4 L 10.0000 11.0000 11.4900
3 R 17.0000 18.7000 18.7500
2 M 34.8000 38.2800 38.4900
1 F 61.5000 67.6500 67.7500
(7 rows affected)
Use function to wrap a long sql statement
4>
5> CREATE TABLE authors(
6> au_id varchar(11),
7> au_lname varchar(40) NOT NULL,
8> au_fname varchar(20) NOT NULL,
9> phone char(12) NOT NULL DEFAULT ("UNKNOWN"),
10> address varchar(40) NULL,
11> city varchar(20) NULL,
12> state char(2) NULL,
13> zip char(5) NULL,
14> contract bit NOT NULL
15> )
16> GO
1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1)
2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1)
3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1)
4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1)
5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1)
6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1)
7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1)
8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0)
9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0)
10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1)
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE TABLE titleauthor(
3> au_id varchar(20),
4> title_id varchar(20),
5> au_ord tinyint NULL,
6> royaltyper int NULL
7> )
8> GO
1>
2> insert titleauthor values("1", "2", 1, 60)
3> insert titleauthor values("2", "3", 1, 100)
4> insert titleauthor values("3", "4", 1, 100)
5> insert titleauthor values("4", "5", 1, 100)
6> insert titleauthor values("5", "6", 1, 100)
7> insert titleauthor values("6", "7", 2, 40)
8> insert titleauthor values("7", "8", 1, 100)
9> insert titleauthor values("8", "9", 1, 100)
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE TABLE sales(
3> stor_id char(4) NOT NULL,
4> ord_num varchar(20) NOT NULL,
5> ord_date datetime NOT NULL,
6> qty smallint NOT NULL,
7> payterms varchar(12) NOT NULL,
8> title_id varchar(80)
9> )
10> GO
1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1")
2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1")
3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2")
4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3")
5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3")
6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2")
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> CREATE FUNCTION dbo.fnSalesCount(@SalesQty bigint)
3> RETURNS TABLE
4> AS
5> RETURN (SELECT au.au_id,
6> au.au_lname + ", " + au.au_fname AS au_name,
7> au.address,
8> au.city + ", " + au.state + " " + zip AS Address2
9> FROM authors au
10> JOIN titleauthor ta
11> ON au.au_id = ta.au_id
12> JOIN sales s
13> ON ta.title_id = s.title_id
14> GROUP BY au.au_id,
15> au.au_lname + ", " + au.au_fname,
16> au.address,
17> au.city + ", " + au.state + " " + zip
18> HAVING SUM(qty) > @SalesQty
19> )
20> GO
1>
2> SELECT *
3> FROM dbo.fnSalesCount(25)
4> GO
au_id au_name address Address2
----------- -------------------------------------------------------------- ---------------------------------------- ------------------------------
1 Joe, Abra 6 St. Berkeley, CA 11111
2 Jack, Majo 3 St. Oakland, CA 22222
(2 rows affected)
1> drop FUNCTION dbo.fnSalesCount;
2> GO
1>
2> drop table authors;
3> GO
1> drop table titleauthor;
2> drop table sales;
3> GO
1>