SQL Server/T-SQL Tutorial/System Settings/rowcount
Содержание
- 1 adds the account column to the authors table and creates the new bank and brokerage tables.
- 2 EXEC and @@ROWCOUNT
- 3 if @@ROWCOUNT = 0, return
- 4 if @@ROWCOUNT > 1, rollback
- 5 @@rowcount can be used to verify the success of selected operations
- 6 Updating an end user"s expiration date.
- 7 Using the SET ROWCOUNT Option
adds the account column to the authors table and creates the new bank and brokerage tables.
3>
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>
3> ALTER TABLE authors
4> ADD account VARCHAR(10) NULL
5> GO
1> CREATE TABLE bank
2> (
3> account VARCHAR(10) NOT NULL PRIMARY KEY,
4> name VARCHAR(50) NOT NULL
5> )
6> GO
1> CREATE TABLE brokerage
2> (
3> account VARCHAR(10) NOT NULL PRIMARY KEY,
4> name VARCHAR(50) NOT NULL
5> )
6> GO
1> INSERT bank VALUES ("ABC", "First Bank")
2> INSERT bank VALUES ("DEF", "Second Bank")
3> INSERT bank VALUES ("XYZ", "Third Bank")
4> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> INSERT brokerage VALUES ("123", "First Broker")
2> INSERT brokerage VALUES ("456", "Second Broker")
3> INSERT brokerage VALUES ("987", "Third Broker")
4> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> sp_addmessage 56000, 10, "Attempt to insert or update an invalid account."
2> GO
1>
2>
3> CREATE TRIGGER trAuthors_InsUpd ON Authors
4> FOR UPDATE
5> AS
6> DECLARE @intRowCount int
7> SELECT @intRowCount = @@RowCount
8> IF @intRowCount > 0
9> BEGIN
10> IF (NOT EXISTS
11> (
12> SELECT account FROM bank WHERE account IN
13> (SELECT account FROM inserted)
14> UNION
15> SELECT account FROM brokerage WHERE account IN
16> (SELECT account FROM inserted)
17> )
18> )
19> BEGIN
20> RAISERROR(56000, 10, 1)
21> ROLLBACK TRANSACTION
22> RETURN
23> END
24> END
25> GO
1>
2> drop TRIGGER trAuthors_InsUpd;
3> drop table authors;
4> drop table bank;
5> drop table brokerage;
6> GO
EXEC and @@ROWCOUNT
4>
5> CREATE TABLE Customers (
6> CustomerID nchar (5) NOT NULL ,
7> CompanyName nvarchar (40) NOT NULL ,
8> ContactName nvarchar (30) NULL ,
9> ContactTitle nvarchar (30) NULL ,
10> Address nvarchar (60) NULL ,
11> City nvarchar (15) NULL ,
12> Region nvarchar (15) NULL ,
13> PostalCode nvarchar (10) NULL ,
14> Country nvarchar (15) NULL ,
15> Phone nvarchar (24) NULL ,
16> Fax nvarchar (24) NULL
17> )
18> GO
1>
2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111")
3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL)
4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444")
5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55")
6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777")
7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32")
8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99")
9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41")
10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745")
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>
2> EXEC("SELECT * FROM Customers")
3> SELECT "The Rowcount is " + CAST(@@ROWCOUNT as varchar)
4> GO
CustomerID CompanyName ContactName ContactTitle Address City Region
PostalCode Country Phone Fax
---------- ---------------------------------------- ------------------------------ ------------------------------ ------------------------------------------------------------ --------------- ---------
------ ---------- --------------- ------------------------ ------------------------
1 A Maria Sales Str. 57 Berlin NULL
12209 Germany 111-1111111 111-1111111
2 M Joe Owner Ave. 231 Vancouver NULL
05023 Mexico (222) 222-3332 NULL
3 H Thomas Sales Sq. 111 London NULL
1D00P UK (444) 444-4444 (444) 444-4444
4 B Berg Order Blv 8 Toronto NULL
00222 Sweden 4444-55 55 65 5555-55 55 55
5 S Moos Sales Fort 57 New York NULL
68306 Germany 6666-66666 6666-77777
6 F Cite Manager 24 Dalles NULL
67000 France 88.60.15.31 88.60.15.32
7 C Sommer Owner Araq, 67 Paris NULL
28023 Spain (91) 555 22 82 (91) 555 91 99
8 P Leb Owner 12 Beijing NULL
13008 France 91.24.45.40 91.24.45.41
9 D Elizabeth Manager 23 Blvd. Tsawassen BC
T2F8M4 Canada (604) 555-4729 (604) 555-3745
(9 rows affected)
----------------------------------------------
The Rowcount is 9
(1 rows affected)
1>
2> drop table Customers;
3> GO
1>
if @@ROWCOUNT = 0, return
3> CREATE TABLE BUDGET
4> (dept_name varchar(30) not null,
5> parent_name varchar(30) null,
6> budget_amt money not null)
7> GO
1> INSERT INTO budget values ("Internal Training", "Training", $10)
2> INSERT INTO budget values ("Training", "Services", $100)
3> INSERT INTO budget values ("Services", NULL, $500)
4> GO
1>
2> CREATE TRIGGER update_budget
3> ON budget FOR update AS
4> DECLARE @rows int
5> SELECT @rows = @@ROWCOUNT
6> IF (@rows=0) RETURN
7> IF (@rows > 1) BEGIN
8> PRINT "Only one row can be updated at a time"
9> ROLLBACK TRAN
10> RETURN
11> END
12> IF (SELECT parent_name FROM inserted) IS NULL RETURN
13> UPDATE budget
14> SET budget_amt = budget_amt + (SELECT budget_amt FROM inserted) -
15> (SELECT budget_amt FROM deleted)
16> WHERE dept_name = (SELECT parent_name FROM inserted)
17>
18>
19> drop TRIGGER update_budget;
20> GO
1>
2> drop table BUDGET;
3> GO
if @@ROWCOUNT > 1, rollback
3> CREATE TABLE BUDGET
4> (dept_name varchar(30) not null,
5> parent_name varchar(30) null,
6> budget_amt money not null)
7> GO
1> INSERT INTO budget values ("Internal Training", "Training", $10)
2> INSERT INTO budget values ("Training", "Services", $100)
3> INSERT INTO budget values ("Services", NULL, $500)
4> GO
1>
2> CREATE TRIGGER update_budget
3> ON budget FOR update AS
4> DECLARE @rows int
5> SELECT @rows = @@ROWCOUNT
6> IF (@rows=0) RETURN
7> IF (@rows > 1) BEGIN
8> PRINT "Only one row can be updated at a time"
9> ROLLBACK TRAN
10> RETURN
11> END
12> IF (SELECT parent_name FROM inserted) IS NULL RETURN
13> UPDATE budget
14> SET budget_amt = budget_amt + (SELECT budget_amt FROM inserted) -
15> (SELECT budget_amt FROM deleted)
16> WHERE dept_name = (SELECT parent_name FROM inserted)
17>
18>
19> drop TRIGGER update_budget;
20> GO
1>
2> drop table BUDGET;
3> GO
@@rowcount can be used to verify the success of selected operations
After each Transact-SQL statement, the server sets the value of this variable to the total
number of records affected by it.
9> select 1+2
10> GO
-----------
3
(1 rows affected)
1>
2> if @@rowcount = 0
3> Print "No rows were copied!"
4> GO
1>
Updating an end user"s expiration date.
3>
4>
5> CREATE TABLE myusers(
6> UserID varchar(30)NOT NULL PRIMARY KEY,
7> FirstName varchar(30),
8> LastName varchar(30),
9> EmployeeType char(1) NOT NULL,
10> DBAccess varchar(30),
11> StartDate datetime,
12> ExpDate datetime
13> )
14> GO
1>
2> CREATE PROC pr_updateuser(@UserID varchar(30), @NewExpDate datetime)
3> AS
4> UPDATE myusers
5> SET ExpDate = @NewExpDate
6> Where UserID = @UserID
7> IF @@rowcount = 1
8> PRINT "User Updated"
9> ELSE
10> PRINT "Invalid UserID entered"
11> GO
1>
2> drop PROC pr_updateuser;
3> GO
1>
2> drop table myusers;
3> GO
Using the SET ROWCOUNT Option
The SET ROWCOUNT option is a session option.
TOP is a query option.
SET ROWCOUNT determines the number of rows affected by a statement.
Syntax for SET ROWCOUNT
SET ROWCOUNT n
If you set it to 0, then the row limit is removed.
Resetting ROWCOUNT
SET ROWCOUNT 0
9> create table Billings (
10> BankerID INTEGER,
11> BillingNumber INTEGER,
12> BillingDate datetime,
13> BillingTotal INTEGER,
14> TermsID INTEGER,
15> BillingDueDate datetime ,
16> PaymentTotal INTEGER,
17> CreditTotal INTEGER
18>
19> );
20> 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> DECLARE @MyIdentity int, @MyRowCount int
3>
4> INSERT Bankers (BankerName)
5> VALUES ("Peerless Binding")
6>
7>
8> SET @MyIdentity = @@IDENTITY
9> SET @MyRowCount = @@ROWCOUNT
10>
11> IF @MyRowCount = 1
12> INSERT INTO Billings VALUES (@MyIdentity, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
13> GO
(1 rows affected)
(1 rows affected)
1>
2>
3> drop table Bankers;
4> GO
1>
2> drop table Billings;
3> GO
1>
2>