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