Post
Ask Question

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.
  1. CREATE TABLE tblEmployee1  
  2. (  
  3.  Id int Primary Key,  
  4.  Name nvarchar(30),  
  5.  Gender nvarchar(10),  
  6.  DepartmentId int  
  7. )  
  8.   
  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')   
  5.   
  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; 
Next Recommended Reading Local And Global Temporary Tables Using Stored Procedure In SQL Server