www.webdeveloper.com
Results 1 to 3 of 3

Thread: SQL Server 2000 Stored Procedure - SET NOCOUNT ON

  1. #1
    Join Date
    May 2003
    Posts
    599

    Question SQL Server 2000 Stored Procedure - SET NOCOUNT ON

    I'm a complete newbie to SQL Server stored procedures (SP), and I have a simple question about SET NOCOUNT. I want to add SET NOCOUNT ON to each SP to speed up the performance a bit, but I'm not sure where to place it. Is there any uniform place to put it within a stored procedure, because I've seen it placed at the beginning, middle, and end of a SP statement. I'm figuring from that that it doesn't matter where it's placed, but I wanted to make sure. This is how I have it placed currently:

    CREATE PROCEDURE [dbo].[spTitle]
    @ID int,
    @Col1 varchar (30),
    @Col2 varchar (30),
    @Col3 varchar (30),
    AS

    SET NOCOUNT ON

    UPDATE tblTitle
    SET
    Col1 = @Col1,
    Col2 = @Col2,
    Col3 = @Col3,
    WHERE ID = @ID
    GO

    Thanks for any help.
    KWilliams
    -----------------------
    It's the end of the world as we know it...and I feel fine

  2. #2
    Join Date
    Apr 2006
    Location
    Ukraine
    Posts
    5
    SET NOCOUNT ON/OFF is using for disabling messages about counting of affected rows in select, insert, update or delete queries... So you must put it before one of this queries which you want to disable rows count message in...
    And don't forget to return in the prevoius state... in the end of the stored procedure I advice you to put 'SET NOCOUNT OFF'...
    Last edited by russellsoft; 04-07-2006 at 02:23 PM.

  3. #3
    Join Date
    May 2003
    Posts
    599
    Great! Thanks for the info russelsoft
    KWilliams
    -----------------------
    It's the end of the world as we know it...and I feel fine

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles