@@ROWCOUNT
This is sql
server global variable and returns the number rows affected by last statements.
Return Type: INT
Syntax: -
@@ROWCOUNT
Example:-
--declare variable to store the affected rows DECLARE @NoOfRowAffected varchar(10)=null UPDATE Employee SET Name = 'Alok Kumar Singh' WHERE Id = 1 -- assigning number of affected rows SET @NoOfRowAffected=@@ROWCOUNT IF @@ROWCOUNT = 0 PRINT 'Warning: No rows were updated'; ELSE PRINT @NoOfRowAffected +' rows updated'
Remark Point:-
If you have a
process that can affected greater than 4 Bytes data or more that 2 billion rows,
then @@ROWCOUNT will throw an exception because its return type is INT.
In situation
you can use ROWCOUNT_BIG sql built-in function same like @@ROWCOUNT. ROWCOUNT_BIG return type is BIGINT
Example:-
--declare variable to store the affected rows DECLARE @NoOfRowAffected varchar(10)=null UPDATE Employee SET Name = 'Alok Kumar Singh' WHERE Id = 1 -- assigning number of affected rows SET @NoOfRowAffected=ROWCOUNT_BIG() IF ROWCOUNT_BIG() = 0 PRINT 'Warning: No rows were updated'; ELSE PRINT @NoOfRowAffected +' rows updated'
Difference between @@ROWCOUNT and ROWCOUNT_BIG
- @@ROWCOUNT is global variable while ROWCOUNT_BIG is sql build-in function
- @@ROWCOUNT Return type is INT while ROWCOUNT_BIG return type is BIGINT
- @ROWCOUNT store 4 Bytes value while ROWCOUNT_BIG can store 8 Bytes value
No comments:
Post a Comment
Note: only a member of this blog may post a comment.