From T-SQL Manual:
Syntax
SET NOCOUNT { ON | OFF }
Remarks
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft® SQL ServerT to execute queries, the results prevent "nn rows affected" from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.
For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced.
The setting of SET NOCOUNT is set at execute or run time and not at parse time.
"Leon" <vn*****@msn.com> ha scritto nel messaggio news:OV**************@TK2MSFTNGP10.phx.gbl...
DOES SETTING NOCOUNT ON option affect my stored procedures and website
performance in anyway within my asp.net application?
When and when not to set SET NOCOUNT ON?
I know what it does but not exactly where and when I want to use it.
i.e.
CREATE PROCEDURE GetAuthorization 2.0
@Username Varchar( 20 ),
@Password Varchar( 16 )
as
SET NOCOUNT ON
declare @AccountID int
set @AccountID = -1
select @AccountID =
case
when Password = @Password and Active = 1 then AccountID
when Password = @Password then -2
else -3
end
from Account
where Username = @Username
return @AccountID
go