SET NOCOUNT ON - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SET NOCOUNT ON

Author

Topic

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-29 : 01:43:52

hi
in stored procedures i don't want to display message as
these many rows

eg: (1 row(s) returned)
for that i wrote
SET NOCOUNT ON
but also it displays
is their any ather solution
or else iam going in wrong way

Malathi Rao

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-29 : 01:44:59

Can you post the SP code?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-29 : 01:45:47

quote:
Originally posted by harsh_athalye

Can you post the SP code?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ALTER PROCEDURE usp_r_select_requirement
@req_id varchar(50)
,@search varchar(50)
,@sname varchar(50)
,@stat int
,@operator char(1)
AS
SET NOCOUNT ON
if (@operator='F')
begin
--SELECT * FROM Requirement

------select statement to display data into grid when id given

if(@req_id!='') AND (@search='') AND (@sname='')
begin
Select r.Id,r.Requirement_Id,r.Source_Id,s.Sname,r.Type,r.Requirement_Date,r.Environment,r.Status
from Rpo.dbo.Requirement r (nolock)
JOIN Rpo.dbo.Source s (nolock) on s.Source_Id=r.Source_Id
where r.Requirement_Id=@req_id

end
------select statement to display data into grid when id,fdate,todate are given

ELSE IF(@req_id!='')AND (@search!='') AND (@sname!='')
BEGIN
Select r.Id,r.Requirement_Id,r.Source_Id,s.Sname,r.Type,r.Requirement_Date,r.Environment,r.Status
from Rpo.dbo.Requirement r (nolock)
JOIN Rpo.dbo.Source s (nolock) on s.Source_Id=r.Source_Id
where r.Requirement_Id=@req_id and r.Requirement_Date Between @search and @sname and r.Status=@stat

END
------select statement to display data into grid when id,fdate,todate are given

ELSE IF(@req_id!='')AND (@search!='') AND (@sname='')
BEGIN
Select r.Id,r.Requirement_Id,r.Source_Id,s.Sname,r.Type,r.Requirement_Date,r.Environment,r.Status
from Rpo.dbo.Requirement r (nolock)
JOIN Rpo.dbo.Source s (nolock) on s.Source_Id=r.Source_Id
where r.Requirement_Id=@req_id and r.Requirement_Date >= @search and r.Status=@stat

END
------select statement to display data into grid when fdate,todate are given

Else if(@search!='') AND (@sname!='')
begin
Select r.Id,r.Requirement_Id,r.Source_Id,s.Sname,r.Type,r.Requirement_Date,r.Environment,r.Status
from Rpo.dbo.Requirement r (nolock)
JOIN Rpo.dbo.Source s (nolock) on s.Source_Id=r.Source_Id
where r.Requirement_Date Between @search and @sname and r.Status=@stat

end
------select statement to display data into grid when fdate,todate are given

Else if(@search!='') AND (@sname='')
begin
Select r.Id,r.Requirement_Id,r.Source_Id,s.Sname,r.Type,r.Requirement_Date,r.Environment,r.Status
from Rpo.dbo.Requirement r (nolock)
JOIN Rpo.dbo.Source s (nolock) on s.Source_Id=r.Source_Id
where r.Requirement_Date >= @search and r.Status=@stat

end
else
------select statement to display data into grid when form loads
begin
Select r.Id,r.Requirement_Id,r.Source_Id,s.Sname,r.Type,r.Requirement_Date,r.Environment,r.Status
from Rpo.dbo.Requirement r (nolock)
JOIN Rpo.dbo.Source s (nolock) on s.Source_Id=r.Source_Id
where r.Status=@stat
end

end

Malathi Rao

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-29 : 01:47:24

SET NOCOUNT ON will turn off the display message. (1 row(s) returned) will be suppressed. Where do you include this line in your stored procedure ? Can you post your code here ?

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-29 : 01:49:43

quote:
Originally posted by khtan

SET NOCOUNT ON will turn off the display message. (1 row(s) returned) will be suppressed. Where do you include this line in your stored procedure ? Can you post your code here ?

KH

ALTER PROCEDURE usp_r_select_requirement
@req_id varchar(50)
,@search varchar(50)
,@sname varchar(50)
,@stat int
,@operator char(1)
AS
SET NOCOUNT ON
if (@operator='F')
begin
--SELECT * FROM Requirement

------select statement to display data into grid when id given

if(@req_id!='') AND (@search='') AND (@sname='')
begin
Select r.Id,r.Requirement_Id,r.Source_Id,s.Sname,r.Type,r.Requirement_Date,r.Environment,r.Status
from Rpo.dbo.Requirement r (nolock)
JOIN Rpo.dbo.Source s (nolock) on s.Source_Id=r.Source_Id
where r.Requirement_Id=@req_id

end
------select statement to display data into grid when id,fdate,todate are given

ELSE IF(@req_id!='')AND (@search!='') AND (@sname!='')
BEGIN
Select r.Id,r.Requirement_Id,r.Source_Id,s.Sname,r.Type,r.Requirement_Date,r.Environment,r.Status
from Rpo.dbo.Requirement r (nolock)
JOIN Rpo.dbo.Source s (nolock) on s.Source_Id=r.Source_Id
where r.Requirement_Id=@req_id and r.Requirement_Date Between @search and @sname and r.Status=@stat

END
------select statement to display data into grid when id,fdate,todate are given

ELSE IF(@req_id!='')AND (@search!='') AND (@sname='')
BEGIN
Select r.Id,r.Requirement_Id,r.Source_Id,s.Sname,r.Type,r.Requirement_Date,r.Environment,r.Status
from Rpo.dbo.Requirement r (nolock)
JOIN Rpo.dbo.Source s (nolock) on s.Source_Id=r.Source_Id
where r.Requirement_Id=@req_id and r.Requirement_Date >= @search and r.Status=@stat

END
------select statement to display data into grid when fdate,todate are given

Else if(@search!='') AND (@sname!='')
begin
Select r.Id,r.Requirement_Id,r.Source_Id,s.Sname,r.Type,r.Requirement_Date,r.Environment,r.Status
from Rpo.dbo.Requirement r (nolock)
JOIN Rpo.dbo.Source s (nolock) on s.Source_Id=r.Source_Id
where r.Requirement_Date Between @search and @sname and r.Status=@stat

end
------select statement to display data into grid when fdate,todate are given

Else if(@search!='') AND (@sname='')
begin
Select r.Id,r.Requirement_Id,r.Source_Id,s.Sname,r.Type,r.Requirement_Date,r.Environment,r.Status
from Rpo.dbo.Requirement r (nolock)
JOIN Rpo.dbo.Source s (nolock) on s.Source_Id=r.Source_Id
where r.Requirement_Date >= @search and r.Status=@stat

end
else
------select statement to display data into grid when form loads
begin
Select r.Id,r.Requirement_Id,r.Source_Id,s.Sname,r.Type,r.Requirement_Date,r.Environment,r.Status
from Rpo.dbo.Requirement r (nolock)
JOIN Rpo.dbo.Source s (nolock) on s.Source_Id=r.Source_Id
where r.Status=@stat
end

end

Malathi Rao

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-29 : 01:59:50

I can't see how you are getting the message. Are you sure this is the SP which is at fault?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-29 : 02:02:13

quote:
Originally posted by harsh_athalye

I can't see how you are getting the message. Are you sure this is the SP which is at fault?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

i got o/p as
Id Requirement_Id Source_Id Sname Type Requirement_Date Environment Status
----------- -------------------------------------------------- ----------- -------------------------------------------------- -------------------------------------------------- ----------------------- -------------------------------------------------- -----------
2 REQ2 4 MICROSOFT Internal 4/30/2007 shsd 1
No rows affected.
(1 row(s) returned)

Malathi Rao

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-29 : 02:05:50

try to apply your stored procedure again. Your SP looks fine to me

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-29 : 02:09:07

use sp_recompile to compile your SP again and try running it.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-29 : 02:10:33