Introduction

SET NOCOUNT ON and SET NOCOUNT OFF will show the status of Transact-SQL statement.

DeScription

SET NOCOUNT ON- It will show "Command(s) completed successfully" .
SET NOCOUNT OFF- it will show "(No. Of row(s) affected)" .
Steps to follow

Create two tables, which are mentioned below.
  1. CREATE TABLE tblEmployee1
  2. (
  3. Id int Primary Key,
  4. Name nvarchar(30),
  5. Gender nvarchar(10),
  6. DepartmentId int
  7. )
  9. CREATE TABLE tblDepartment1
  10. (
  11. DeptId int Primary Key,
  12. DeptName nvarchar(20)
  13. )
Insert some dummy records in these tables.
  1. Insert into tblDepartment1 values (1,'Blog')
  2. Insert into tblDepartment1 values (2,'Article')
  3. Insert into tblDepartment1 values (3,'Resource')
  4. Insert into tblDepartment1 values (4,'Book')
  6. Insert into tblEmployee1 values (1,'Satya1', 'Male', 3)
  7. Insert into tblEmployee1 values (2,'Satya2', 'Male', 2)
  8. Insert into tblEmployee1 values (3,'Satya3', 'Female', 1)
  9. Insert into tblEmployee1 values (4,'Satya4', 'Male', 4)
  10. Insert into tblEmployee1 values (5,'Satya5', 'Female', 1)
  11. Insert into tblEmployee1 values (6,'Satya6', 'Male', 3)
Create one stored procedure, using SET NOCOUNT ON.
  1. Create Procedure Sp_CountON
  2. As
  3. Begin
  4. set Nocount on;
  5. Select t1.Name,t1.Gender,t2.DeptName from tblEmployee1 t1
  6. inner join tblDepartment1 t2 on t1.DepartmentId = t2.DeptId
  7. end
Create one stored procedure, using SET NOCOUNT OFF.
  1. Create Procedure Sp_CountOFF
  2. As
  3. Begin
  4. set Nocount off;
  5. Select t1.Name,t1.Gender,t2.DeptName from tblEmployee1 t1
  6. inner join tblDepartment1 t2 on t1.DepartmentId = t2.DeptId
  7. end
Execute these procedures.

For SET NOCOUNT ON;
  1. exec Sp_CountON

For SET NOCOUNT OFF;
  1. exec Sp_CountOFF
Summary
  1. What is SET NOCOUNT ON and SET NOCOUNT OFF, Using Stored Procedure In SQL Server.
  2. How to implement it in Stored Procedure.
  3. Check the results by executing stored procedure.
  4. In real time, always use SET NOCOUNT ON;