Di SQL Server, Anda dapat menggunakan fungsi sistem @@ROWCOUNT
untuk mengembalikan jumlah baris yang terpengaruh oleh pernyataan T-SQL terakhir
Misalnya, jika kueri mengembalikan 4 baris, @@ROWCOUNT
akan mengembalikan 4
Contoh 1 – Memilih Data
Berikut adalah contoh dasar untuk menunjukkan cara kerjanya
SELECT * FROM Dogs;
SELECT @@ROWCOUNT;
Hasil
+---------+-----------+-----------+ | DogId | DogName | GoodDog | |---------+-----------+-----------| | 1 | Fetch | 0 | | 2 | Fluffy | 0 | | 3 | Wag | 0 | +---------+-----------+-----------+ [3 rows affected] +--------------------+ | [No column name] | |--------------------| | 3 | +--------------------+ [1 row affected]_
Dalam hal ini, pernyataan
+---------+-----------+-----------+ | DogId | DogName | GoodDog | |---------+-----------+-----------| | 1 | Fetch | 0 | | 2 | Fluffy | 0 | | 3 | Wag | 0 | +---------+-----------+-----------+ [3 rows affected] +--------------------+ | [No column name] | |--------------------| | 3 | +--------------------+ [1 row affected]_0 saya mengembalikan 3 baris, sehingga
@@ROWCOUNT
mengembalikan 3Contoh 2 – Memperbarui Data
Berikut adalah contoh penggunaan @@ROWCOUNT
dengan pernyataan
+---------+-----------+-----------+ | DogId | DogName | GoodDog | |---------+-----------+-----------| | 1 | Fetch | 0 | | 2 | Fluffy | 0 | | 3 | Wag | 0 | +---------+-----------+-----------+ [3 rows affected] +--------------------+ | [No column name] | |--------------------| | 3 | +--------------------+ [1 row affected]3 untuk menguji apakah ada baris yang diperbarui atau tidak
UPDATE Dogs
SET GoodDog = 1
WHERE DogId = 4
IF @@ROWCOUNT > 0
PRINT 'Your dog will be rewarded accordingly';
ELSE
PRINT 'A dog outside the system cannot be a good dog'
GO
_Hasil
[0 rows affected] A dog outside the system cannot be a good dog
Dalam hal ini, tidak ada baris yang diperbarui karena DogId tidak ada di tabel. Kami dapat menggunakan @@ROWCOUNT
dengan pernyataan
+---------+-----------+-----------+ | DogId | DogName | GoodDog | |---------+-----------+-----------| | 1 | Fetch | 0 | | 2 | Fluffy | 0 | | 3 | Wag | 0 | +---------+-----------+-----------+ [3 rows affected] +--------------------+ | [No column name] | |--------------------| | 3 | +--------------------+ [1 row affected]5 untuk mengembalikan pesan yang sesuai kepada pengguna
Ini dia lagi, tapi kali ini anjingnya memang ada
UPDATE Dogs
SET GoodDog = 1
WHERE DogId = 1
IF @@ROWCOUNT > 0
PRINT 'Your dog will be rewarded accordingly';
ELSE
PRINT 'A dog outside the system cannot be a good dog'
GO
Hasil
[1 row affected] Your dog will be rewarded accordingly_
Kumpulan Data yang Sangat Besar?
Jika menurut Anda jumlah baris yang dipengaruhi oleh pernyataan akan lebih dari 2 miliar, gunakan
+---------+-----------+-----------+ | DogId | DogName | GoodDog | |---------+-----------+-----------| | 1 | Fetch | 0 | | 2 | Fluffy | 0 | | 3 | Wag | 0 | +---------+-----------+-----------+ [3 rows affected] +--------------------+ | [No column name] | |--------------------| | 3 | +--------------------+ [1 row affected]6 sebagai gantinya
Anda dapat menggunakannya dengan cara yang sama seperti @@ROWCOUNT
digunakan
UPDATE Dogs
SET GoodDog = 1
WHERE DogId = 4
IF ROWCOUNT_BIG[] > 0
PRINT 'Your dog will be rewarded accordingly';
ELSE
PRINT 'A dog outside the system cannot be a good dog'
GO
Hasil
[0 rows affected] A dog outside the system cannot be a good dog
Ketika @@ROWCOUNT
adalah Reset
Pernyataan seperti
+---------+-----------+-----------+ | DogId | DogName | GoodDog | |---------+-----------+-----------| | 1 | Fetch | 0 | | 2 | Fluffy | 0 | | 3 | Wag | 0 | +---------+-----------+-----------+ [3 rows affected] +--------------------+ | [No column name] | |--------------------| | 3 | +--------------------+ [1 row affected]9,
UPDATE Dogs
SET GoodDog = 1
WHERE DogId = 4
IF @@ROWCOUNT > 0
PRINT 'Your dog will be rewarded accordingly';
ELSE
PRINT 'A dog outside the system cannot be a good dog'
GO
0, UPDATE Dogs
SET GoodDog = 1
WHERE DogId = 4
IF @@ROWCOUNT > 0
PRINT 'Your dog will be rewarded accordingly';
ELSE
PRINT 'A dog outside the system cannot be a good dog'
GO
1, UPDATE Dogs
SET GoodDog = 1
WHERE DogId = 4
IF @@ROWCOUNT > 0
PRINT 'Your dog will be rewarded accordingly';
ELSE
PRINT 'A dog outside the system cannot be a good dog'
GO
2, UPDATE Dogs
SET GoodDog = 1
WHERE DogId = 4
IF @@ROWCOUNT > 0
PRINT 'Your dog will be rewarded accordingly';
ELSE
PRINT 'A dog outside the system cannot be a good dog'
GO
3, UPDATE Dogs
SET GoodDog = 1
WHERE DogId = 4
IF @@ROWCOUNT > 0
PRINT 'Your dog will be rewarded accordingly';
ELSE
PRINT 'A dog outside the system cannot be a good dog'
GO
4, UPDATE Dogs
SET GoodDog = 1
WHERE DogId = 4
IF @@ROWCOUNT > 0
PRINT 'Your dog will be rewarded accordingly';
ELSE
PRINT 'A dog outside the system cannot be a good dog'
GO
5, atau UPDATE Dogs
SET GoodDog = 1
WHERE DogId = 4
IF @@ROWCOUNT > 0
PRINT 'Your dog will be rewarded accordingly';
ELSE
PRINT 'A dog outside the system cannot be a good dog'
GO
6 mengatur ulang nilai @@ROWCOUNT
menjadi ________4______8