SET NOCOUNT ON And SET NOCOUNT OFF Using Stored Procedure In SQL Server

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.

CREATE TABLE tblEmployee1
(
 Id int Primary Key,
 Name nvarchar(30),
 Gender nvarchar(10),
 DepartmentId int
)

CREATE TABLE tblDepartment1
(
 DeptId int Primary Key,
 DeptName nvarchar(20)
)

Insert some dummy records in these tables.

Insert into tblDepartment1 values (1,'Blog')
Insert into tblDepartment1 values (2,'Article')
Insert into tblDepartment1 values (3,'Resource')
Insert into tblDepartment1 values (4,'Book')

Insert into tblEmployee1 values (1,'Satya1', 'Male', 3)
Insert into tblEmployee1 values (2,'Satya2', 'Male', 2)
Insert into tblEmployee1 values (3,'Satya3', 'Female', 1)
Insert into tblEmployee1 values (4,'Satya4', 'Male', 4)
Insert into tblEmployee1 values (5,'Satya5', 'Female', 1)
Insert into tblEmployee1 values (6,'Satya6', 'Male', 3)

Create one stored procedure, using SET NOCOUNT ON.

Create Procedure Sp_CountON
As
Begin
set Nocount on;
Select t1.Name,t1.Gender,t2.DeptName from tblEmployee1 t1
inner join tblDepartment1 t2 on t1.DepartmentId = t2.DeptId
end

Create one stored procedure, using SET NOCOUNT OFF.

Create Procedure Sp_CountOFF
As
Begin
set Nocount off;
Select t1.Name,t1.Gender,t2.DeptName from tblEmployee1 t1
inner join tblDepartment1 t2 on t1.DepartmentId = t2.DeptId
end

Execute these procedures.

For SET NOCOUNT ON;

exec Sp_CountON

 

 

For SET NOCOUNT OFF; 

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;