SQL Server/T-SQL Tutorial/Transact SQL/Print — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 10:22, 26 мая 2010
Содержание
PRINT command
6> PRINT "Hello World"
7> GO
Hello World
1>
2>
Print debug string out of a code block
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
Printing with variables.
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
PRINT string_expression
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
PRINT "Today is " + CONVERT(char(30), GETDATE())
7> PRINT "Today is " + CONVERT(char(30), GETDATE())
8> GO
Today is Aug 17 2008 1:19PM
1>
2>
3>