Use @@ROWCOUNT wisely

Writing @@ROWCOUNT right after the SET NOCOUNT ON will result in wrong data fetching. To prove my point :-

Create a stored procedure and copy-paste the code below:-

Create PROCEDURE demo_procedure	
AS
BEGIN
	SET NOCOUNT ON 
	select @@ROWCOUNT;
	SELECT * from tbl_emp;	
END

You will get zero as a return value or number of columns affected as zero. Why because the @@ROWCOUNT is written right after the SET NOCOUNT ON.
Try writing the code as below :-

Create PROCEDURE demo_procedure	
AS
BEGIN
	SET NOCOUNT ON 
	SELECT * from tbl_emp;	
        select @@ROWCOUNT;
END

Executes the above code and see the difference !


Thanks and Regards
Akiii