SQL Server/T-SQL Tutorial/Transact SQL/Print
Содержание
PRINT command
<source lang="sql">
6> PRINT "Hello World" 7> GO Hello World 1> 2></source>
Print debug string out of a code block
<source lang="sql">
8> CREATE TABLE Product( 9> ProductID int NOT NULL, 10> Name nvarchar(25) NOT NULL, 11> ProductNumber nvarchar(25) , 12> Color nvarchar(15) NULL, 13> StandardCost money NOT NULL, 14> Size nvarchar(5) NULL, 15> Weight decimal(8, 2) NULL, 16> ProductLine nchar(20) NULL, 17> SellStartDate datetime NOT NULL, 18> SellEndDate datetime NULL 19> ) 20> GO 1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22"); 2> GO (1 rows affected) 1> 2> 3> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22"); 4> GO (1 rows affected) 1> 2> 3> 4> 5> 6> 7> DECLARE @Counter Int 8> SET @Counter = 1 9> WHILE @Counter < 4 10> BEGIN 11> PRINT "SubCategory " 12> SELECT Name, ProductID 13> FROM Product 14> WHERE ProductID = @Counter 15> SET @Counter = @Counter + 1 16> END 17> GO SubCategory Name ProductID
-----------
Product A 1 (1 rows affected) SubCategory Name ProductID
-----------
Product B 2 (1 rows affected) SubCategory Name ProductID
-----------
Product C 3 (1 rows affected) 1> 2> 3> drop table Product; 4> GO</source>
Printing with variables.
<source lang="sql">
8> 9> CREATE TABLE sales( 10> stor_id char(4) NOT NULL, 11> ord_num varchar(20) NOT NULL, 12> ord_date datetime NOT NULL, 13> qty smallint NOT NULL, 14> payterms varchar(12) NOT NULL, 15> title_id varchar(80) 16> ) 17> 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> DECLARE @intMinQty INT, @intNumOrders INT, @chrOutputText CHAR(60) 3> 4> SELECT @intMinQty = 15 5> SELECT @intNumOrders = COUNT(*) FROM sales WHERE qty > @intMinQty 6> SELECT @chrOutputText = "There are " 7> + CONVERT(VARCHAR,@intNumOrders) 8> + " orders with a quantity greater than " 9> + CONVERT(VARCHAR, @intMinQty) 10> PRINT @chrOutputText 11> GO There are 3 orders with a quantity greater than 15 1> 2> drop table sales; 3> GO</source>
PRINT string_expression
<source lang="sql">
A script that uses some of the statements shown above 7> 8> create table Billings ( 9> BankerID INTEGER, 10> BillingNumber INTEGER, 11> BillingDate datetime, 12> BillingTotal INTEGER, 13> TermsID INTEGER, 14> BillingDueDate datetime , 15> PaymentTotal INTEGER, 16> CreditTotal INTEGER 17> 18> ); 19> 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> DECLARE @TotalDue money 4> 5> SET @TotalDue = (SELECT SUM(BillingTotal - PaymentTotal - CreditTotal) 6> FROM Billings) 7> IF @TotalDue > 0 8> PRINT "Total Billings due = $" + CONVERT(varchar,@TotalDue,1) 9> ELSE 10> PRINT "Billings paid in full" 11> GO Billings paid in full 1> 2> 3> drop table Billings; 4> GO</source>
PRINT "Today is " + CONVERT(char(30), GETDATE())
<source lang="sql">
7> PRINT "Today is " + CONVERT(char(30), GETDATE()) 8> GO Today is Aug 17 2008 1:19PM 1> 2> 3></source>
