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

Материал из SQL эксперт
Перейти к: навигация, поиск

A SELECT statement that uses the function in a join operation

   <source lang="sql">

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</source>


Call two user-defined functions in a select statement

   <source lang="sql">

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></source>


Call user-defined function in where clause

   <source lang="sql">

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></source>


Create a scalar-valued function that returns the total Billing amount due

   <source lang="sql">

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></source>


Function WITH SCHEMABINDING

   <source lang="sql">

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></source>


Get the 3 employees with the most RegionPlace

   <source lang="sql">

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</source>


Invoke the scalar-valued function

   <source lang="sql">

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</source>


Pass a declared variable to a function

   <source lang="sql">

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></source>


Query a table returned from a function

   <source lang="sql">

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</source>


Return TOP 100 PERCENT WITH TIES from a function

   <source lang="sql">

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</source>


The syntax for altering a multi-statement table-valued function

   <source lang="sql">

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</source>


The syntax for altering a simple table-valued function

   <source lang="sql">

ALTER FUNCTION [owner_name.]function_name 

   ([@parameter_name data_type [= default]] [, ...])

RETURNS TABLE [WITH {ENCRYPTION|SCHEMABINDING|ENCRYPTION,SCHEMABINDING}] RETURN [(] select_statement [)]</source>


The syntax for creating a multi-statement table-valued function

   <source lang="sql">

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</source>


The syntax for creating a scalar-valued function

   <source lang="sql">

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</source>


The syntax for creating a simple table-valued function

   <source lang="sql">

CREATE FUNCTION [owner_name.]function_name 

   ([@parameter_name data_type [= default]] [, ...])

RETURNS TABLE [WITH {ENCRYPTION|SCHEMABINDING|ENCRYPTION,SCHEMABINDING}] [AS] RETURN [(] select_statement [)]</source>


The syntax of the ALTER FUNCTION statement for a scalar valued function

   <source lang="sql">

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</source>


Use function as a view

   <source lang="sql">

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)</source>


Use function to wrap a long sql statement

   <source lang="sql">

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></source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Procedure_Function/function&oldid=7060»