SubSeleect Row Count - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SubSeleect Row Count

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 07:43:39

I wasn't expecting this to set @@ROWCOUNT=1 :-(


DECLARE @MyPK_ID int

SELECT	@MyPK_ID =
(
	SELECT TOP 1 MyPK_ID
	FROM	dbo.MyTable
	WHERE	MyOtherColumn = 'NeverMatchThis'
	ORDER BY MyCreateDate, MyPK_ID	-- Oldest first
)
SELECT [@MyPKID]=@MyPKID, [@@ROWCOUNT]=@@ROWCOUNT

Result is:
@MyPKID @@ROWCOUNT  
------- ----------- 
NULL    1

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-05 : 07:50:34

Ummmmmm, why not? It's got two SELECT statements in it.

This should blow your mind:

print @@rowcount
declare @a datetime
print @@rowcount
set @a=getdate()
print @@rowcount
select @a=getdate()
print @@rowcount
select @a=getdate() from sysobjects
print @@rowcount

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 08:38:43

Yeah, so I need to try harder. Class is always the same :-(

But I spent a good age relying on the @@ROWCOUNT falling to 0 [which never happened] where there was no more to process, instead of checking that the selected value was coming back NULL. Je suis un plonker!

Kristen

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 08:42:55

I guessed the second one wrongly ...

print @@rowcount -- 0 or n - depending on previous task
declare @a datetime
print @@rowcount -- No value here
set @a=getdate()
print @@rowcount -- 1 - same as a SELECT
select @a=getdate()
print @@rowcount -- Ditto
select @a=getdate() from sysobjects
print @@rowcount -- COUNT(*) from sysobjects

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources