@@RowCount difference in behaviours

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

SQL Server 2005 Forums

Transact-SQL (2005)

@@RowCount difference in behaviours

Author

Topic

jonathans
Starting Member

40 Posts

Posted - 2007-07-25 : 05:50:08

Hi all

Please could someone help me, or least explain why the following two statements behave differently.

For the 1st one i get back a hundred rows or so. The 2nd one only returns 2 rows where the parent id = @ParentId and no further down

ADDED: This all works fine on SQL 2005 but not on SQL 2000 (will check for a SQL SP)
-- THIS WORKS

DECLARE @ParentID INT
SET @ParentId = 1

DECLARE @Tree TABLE
( [Id] INT,
[levelNo] INT )

INSERT INTO @Tree
SELECT [id], 1
FROM [dbo].[Group]
WHERE ISNULL([ParentId], -1) = ISNULL(@ParentId, ISNULL([ParentId], -1))

DECLARE @RowCount INT
DECLARE @i INT

SET @i = 0
SET @RowCount = @@ROWCOUNT

WHILE @RowCount > 0
BEGIN
SET @i = @i + 1

INSERT INTO @tree
SELECT [Group].[Id], @i + 1
FROM [dbo].[Group]
INNER JOIN @Tree a ON [Group].[ParentId] = a.[Id]
WHERE [levelNo] = @i
SET @RowCount = @@ROWCOUNT
END

SELECT [Id]
FROM @Tree

-- THIS DOESN’T WORK

SELECT * FROM GroupTree(1)

CREATE FUNCTION [dbo].[GroupTree]
( @ParentId INT )
RETURNS @Return TABLE
( [Id] INT )
AS
BEGIN
DECLARE @Tree TABLE
( [Id] INT,
[levelNo] INT )

INSERT INTO @Tree
SELECT [id], 1
FROM [dbo].[Group]
WHERE ISNULL([ParentId], -1) = ISNULL(@ParentId, ISNULL([ParentId], -1))

DECLARE @RowCount INT
DECLARE @i INT
SET @i = 0
SET @RowCount = @@ROWCOUNT

WHILE @RowCount > 0
BEGIN
SET @i = @i + 1

INSERT INTO @tree
SELECT [Group].[Id], @i + 1
FROM [dbo].[Group]
INNER JOIN @Tree a ON [Group].[ParentId] = a.[Id]
WHERE [levelNo] = @i

SET @RowCount = @@ROWCOUNT
END

INSERT INTO @Return
SELECT [Id]
FROM @Tree

RETURN
END

WARNING: Running on cold coffee!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-25 : 11:19:42

Can you provide some sample data from the Group table.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-07-25 : 11:58:09

I wonder if this performs differently?

SET @i = 0
SET @RowCount = @@ROWCOUNT

Perhaps try just a:

SET @RowCount = 1

to force the first loop iteration

Kristen

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-07-25 : 12:53:51

Are you trying to enter the loop every time? Or do you only want to enter the loop if the INSERT before the loop actually inserted rows?

If you just want to enter the loop everytime I'd go with Kristen's suggestion. If the latter, then you need to move the SET @RowCount = @@ROWCOUNT immediatly after the insert.

Kristen
Test

22859 Posts

Posted - 2007-07-25 : 14:41:10

"If you just want to enter the loop everytime ..."

Apart from "performance" its benign. (If there is no work to do the first iteration will insert ZERO rows)

Kristen

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-07-25 : 15:12:51

Kristen,

Aggreed. I was actually trying to get at whether the placement of the SET @RowCount = @@ROWCOUNT before the loop was in the correct spot. As putting it where it is now is pretty meaningless. As we know, @@ROWCOUNT doesn't mean anything unless placed directly after the statement that one wanted to get a row count for. So Should it be:

INSERT INTO @Tree 
SELECT [id], 1 
FROM [dbo].[Group]
WHERE ISNULL([ParentId], -1) = ISNULL(@ParentId, ISNULL([ParentId], -1))

SET @i = 0
SET @RowCount = 1 -- Or any non negative value

WHILE @RowCount > 0
BEGIN 
SET @i = @i + 1

INSERT INTO @tree
	SELECT [Group].[Id], @i + 1
	FROM [dbo].[Group]
	INNER JOIN @Tree a ON [Group].[ParentId] = a.[Id]
	WHERE [levelNo] = @i
	SET @RowCount = @@ROWCOUNT
END

-- OR

DECLARE @RowCount INT
DECLARE @i INT

INSERT INTO @Tree 
SELECT [id], 1 
FROM [dbo].[Group]
WHERE ISNULL([ParentId], -1) = ISNULL(@ParentId, ISNULL([ParentId], -1))

SET @RowCount = @@ROWCOUNT
SET @i = 0

WHILE @RowCount > 0
BEGIN 
	SET @i = @i + 1
	INSERT INTO @tree
	SELECT [Group].[Id], @i + 1
	FROM [dbo].[Group]
	INNER JOIN @Tree a ON [Group].[ParentId] = a.[Id]
	WHERE [levelNo] = @i
	SET @RowCount = @@ROWCOUNT
END

I assumed it would be the latter.

-Ryan

Kristen
Test

22859 Posts

Posted - 2007-07-26 : 01:36:21

Indeed, I agree. I certainly wouldn't recommend setting it initially based on the result of a side effect of the previous

SET @i = 0

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources