March 19, 2004 at 4:31 am
March 19, 2004 at 2:22 pm
Way to go David. I think this is a best practice as it improves the performance of the stored procedures by reducing the network traffic.
Francis
March 19, 2004 at 4:02 pm
And here we we were thinking maybe no one cared about using it.
March 22, 2004 at 1:28 am
Sorry, half the post got lost for some reason.
It should have said that I have a stored procedure where SET NOCOUNT ON doesn't seem to work.
The procedure itterates down through a table with a parent_id, child_id relationship using temporary tables to accumulate the records.
The selections in the application database don't send ROWCOUNT messages but the ones for the TEMPDB tables do.
I was wondering why and how can I stop them?
Sorry for the missed post, I wasn't practicing DBA ZEN
March 22, 2004 at 5:11 am
Are all peices within the same scope. Same SP or in different ones, or maybe are you using dymanic SQL within you SP where the ones for the tempDB are returning. I use temp tables often and have not seen SET NOCOUNT ON not work, but no dynamic SQL and all my SP's even the grouped ones have the SET NOCOUNT ON parameter in them.
March 22, 2004 at 5:26 am
It is one long stored procedure. I am intending to break it out into smaller procedures later, but at this stage I am simply getting the procedure working.
What I am doing is
Once all this is working I will have to look at preventing recompiles. Any guidance on that would be greatly appreciated as well!
March 22, 2004 at 8:55 am
I have found that when I use more than one database in a stored procedure, I have to put SET NOCOUNT ON for each one.
USE TEMPDB
SET NOCOUNT ON
Code
USE MyDB
SET NOCOUNT ON
Code
USE TEMPDB
SET NOCOUNT ON
Code
I believe SET NOCOUNT ON is a setting for the 'Current' database, not for the script.
-SQLBill
March 22, 2004 at 9:00 am
The script doesn't switch to using TEMPDB. It simply references temporary tables.
CREATE TABLE #Tbl_Tree1(...etc)
INSERT #Tbl(... etc)
It is a shame that there isn't a global setting for SET NOCOUNT.
March 25, 2004 at 7:34 pm
Not real sure but I think if you have "GO" anywhere in your script, you need to set nocount to on after the "GO". Of course, if you don't have any "GO"s, just ignore this post.
--Jeff Moden
March 26, 2004 at 1:30 am
The actual procedure is shown below.
The idea is that it returns a "page" of records. A page defaulting to 10 records.
CREATE PROCEDURE dbo.usp_GetChildLinksNav
@lViewTreeId Int,
@lPageLength Int =10 ,
@lPageNo Int = 1 ,
@lLanguageID Int=1 ,
@lSortID Int=1 ,
@lYear Int = -1 ,
@lDateType Int = 0
AS
/*
* PROC:- usp_GetChildLinksNav
*
* Description:- Performs the page n of m retrieval on the usp_GetChildLinks proc.
*
* Arguments:-
* ===========
* @lViewTreeID The root of the tree for which branches will be retrieved.
* @lPageLength The number of links in a page of links.
* @lPageNo The page number requested. If this exceeds the actual number of
* pages available, it will be reset to the last page.
* @lLanguageID the id of the required language.
* @lSortID An identifier to identify the sort order for the records.
* 1 = Title , 2 = Date
*
* Variables:-
* ===========
* @lMaxRows The calculated number of records to retrieve. If this exceeds the
* actual number of records available then this will be reset to
* the actual number of records.
* @lTotalRows The actual number of archived news rows available.
* @lFirstRow The record number of the first record on the page.
* @lPages The actual number of pages available.
*
* Dependancies Proc: usp_GetChildLinks
*
*
* Modified Date: Author Description
* ============== =========== ===========
* 09-Mar-2004 David Poole Created
*/
SET NOCOUNT ON
DECLARE @lTotalRows INT
DECLARE @lMaxRows INT
DECLARE @lFirstRow INT
DECLARE @lPages INT
CREATE TABLE #Tbl_PageInfo1 (
Page_ID Int ,
Title VARCHAR(250) ,
Updated_Date SmallDateTime ,
Valid_From SmallDateTime ,
Valid_Till SmallDateTime ,
Position Int)
CREATE TABLE #Tbl_PageInfo2 (
Page_ID Int ,
Title VARCHAR(250) ,
Updated_Date SmallDateTime ,
Valid_From SmallDateTime ,
Valid_Till SmallDateTime ,
Position Int)
INSERT #Tbl_PageInfo1 ( Page_Id , Title , Updated_Date,Valid_From, Valid_Till, Position )
exec usp_GetChildLinks
@lViewTreeID ,
@lLanguageID ,
@lYear ,
@lDateType
-- How many links were actually returned?
SET @lTotalRows = @@ROWCOUNT
-- Calculate the maximum number of rows to return on the basis of the
-- page length and the page requested.
SET @lMaxRows = @lPageLength * @lPageNo
-- If the calculated rows exceeds the actual number of rows then
-- reset the calculated rows to the actual.
IF @lMaxRows > @lTotalRows OR @lMaxRows=0
SET @lMaxRows = @lTotalRows
-- Calculate the actual number of pages.
IF @lPageLength=0
SET @lPages = 1
ELSE
SET @lPages = CEILING ( CAST ( @lTotalRows AS REAL ) / CAST ( @lPageLength AS REAL) )
-- If the requested page number is after the last page, then set to the last page.
IF @lPageNo > @lPages
SET @lPageNo = @lPages
-- Calculate the first row number.
SET @lFirstRow = @lPageLength * ( @lPageNo - 1 ) + 1
SET @lPageLength = (@lMaxRows - @lFirstRow) +1
SET ROWCOUNT @lMaxRows
IF @lSortID = 1
BEGIN
INSERT #Tbl_PageInfo2
SELECT *
FROM #Tbl_PageInfo1
ORDER BY Title ASC , Page_ID ASC
SET ROWCOUNT @lPageLength
SELECT * ,
@lTotalRows AS TotalLinks ,
@lPages As TotalPages ,
@lFirstRow AS FirstLinkOnPage ,
@lMaxRows AS LastLinkOnPage
FROM #Tbl_PageInfo2
ORDER BY Title DESC, Page_ID DESC
GOTO EndProc
END
IF @lSortID = 2
BEGIN
INSERT #Tbl_PageInfo2
SELECT *
FROM #Tbl_PageInfo1
ORDER BY Valid_From DESC , Page_ID ASC
SET ROWCOUNT @lPageLength
SELECT * ,
@lTotalRows AS TotalLinks ,
@lPages As TotalPages ,
@lFirstRow AS FirstLinkOnPage ,
@lMaxRows AS LastLinkOnPage
FROM #Tbl_PageInfo2
ORDER BY Valid_From ASC, Page_ID DESC
GOTO EndProc
END
IF @lSortID = 3
BEGIN
INSERT #Tbl_PageInfo2
SELECT *
FROM #Tbl_PageInfo1
ORDER BY Valid_Till DESC , Page_ID ASC
SET ROWCOUNT @lPageLength
SELECT * ,
@lTotalRows AS TotalLinks ,
@lPages As TotalPages ,
@lFirstRow AS FirstLinkOnPage ,
@lMaxRows AS LastLinkOnPage
FROM #Tbl_PageInfo2
ORDER BY Valid_Till ASC, Page_ID DESC
GOTO EndProc
END
IF @lSortID = 4
BEGIN
INSERT #Tbl_PageInfo2
SELECT *
FROM #Tbl_PageInfo1
ORDER BY Updated_Date DESC , Page_ID ASC
SET ROWCOUNT @lPageLength
SELECT * ,
@lTotalRows AS TotalLinks ,
@lPages As TotalPages ,
@lFirstRow AS FirstLinkOnPage ,
@lMaxRows AS LastLinkOnPage
FROM #Tbl_PageInfo2
ORDER BY Updated_Date ASC, Page_ID DESC
GOTO EndProc
END
EndProc:
SET ROWCOUNT 0
-- Explicit Drop of temporary table objects.
DROP TABLE #Tbl_PageInfo1
DROP TABLE #Tbl_PageInfo2
GO
March 26, 2004 at 7:57 am
I'm not positive about this, but...
I looked at Batches, overview in the BOL and it says that CREATE command begins each batch. So, it seems there is an 'implied' GO after each CREATE command.
Try moving the SET NOCOUNT ON to the place after the last CREATE and before the INSERT.
-SQLBill
March 26, 2004 at 8:06 am
Perhaps changing the temp tables to tables variables would help as well since declaring table variables is not considered DML and my guess is therefore no implied GO statement.
By the way do you use SET NOCOUNT ON in the proc exec usp_GetChildLinks ?
Francis
March 26, 2004 at 8:33 am
Yes, all stored procedures that I write have SET NOCOUNT ON immediately after the header block.
I haven't tried declaring variables as table types before so I'll give it a whirl.
March 30, 2004 at 11:05 am
David,
Haven't seen any more from you on this, have you tried this solution as I suggested:
CREATE TABLE #Tbl_PageInfo2 (
Page_ID Int ,
Title VARCHAR(250) ,
Updated_Date SmallDateTime ,
Valid_From SmallDateTime ,
Valid_Till SmallDateTime ,
Position Int)
SET NOCOUNT ON
INSERT #Tbl_PageInfo1 ( Page_Id , Title , Updated_Date,Valid_From, Valid_Till, Position )
exec usp_GetChildLinks
@lViewTreeID ,
@lLanguageID ,
@lYear ,
@lDateType
-SQLBill
March 30, 2004 at 3:29 pm
Just out of curiosity are there any triggers that change the NOCOUNT behavior? I've had problems with this in the past where a trigger has played havoc with NOCOUNT settings.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply