@@rowcount assignment

Question

Post reply

@@rowcount assignment

Jules Bonnot

SSCertifiable

Points: 5488
More actions
November 3, 2006 at 5:28 am

#114941

drop
table #test
declare
@rows int
create
table #test (id int identity , val varchar(100))
insert
#test
select
top 100('hello')
from
syscolumns
set
@rows = @@rowcount
insert
#test
select
top (@@rowcount)'bye'
from
syscolumns
select
* from #test

i want to get the row count from a insert statement and then use it in the top clause of another statement but the assigment to the local variable appears to set the rowcount back to one. Anyone got any ideas?
www.sql-library.com[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 1

drop

table #test

declare

@rows int

create

table #test (id int identity , val varchar(100))

insert

#test

select

top 100('hello')

from

syscolumns

set

@rows = @rows + @@rowcount

insert

#test

select

top (@@rowcount)'bye'

from

syscolumns

select

* from #test

www.sql-library.com[/url]

Sergiy SSC Guru Points: 110208 More actions · Answer 2

@@rowcount returns number of rows affected by LAST statement.

Any questions?

_____________
Code for TallyGenerator

John Rowan SSC Guru Points: 56440 More actions · Answer 3

In addition to what Sergiy stated, SQL Server 2000 does not allow you to use a variable value for the number of rows returned by TOP. Look up 'SET ROWCOUNT' in BOL.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

faizjmc SSC Eights! Points: 881 More actions · Answer 4

Can u explain the use of 'set rowcount' with realtime examples. I went thru the Sql help but it didn't impress me..

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 5

Ok... One picture is worth a thousand words...

--===== Conditionally drop the temp table

IF OBJECT_ID('TEMPDB..#Test') IS NOT NULL

DROP TABLE #Test

DECLARE @Rows INT

CREATE TABLE #Test (ID INT IDENTITY(1,1), Val VARCHAR(100))

INSERT #Test (Val)

SELECT TOP 100('HELLO')

FROM SYSCOLUMNS

SET @Rows = @@ROWCOUNT

--===== Limit the number of rows processed for all further queries

SET ROWCOUNT @Rows

INSERT #Test

SELECT 'BYE'

FROM SYSCOLUMNS

--===== Remove limit on number of rows processed for all further queries

SET ROWCOUNT 0

SELECT * FROM #Test

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Rowan SSC Guru Points: 56440 More actions · Answer 6

Thanks for the example Jeff.

Faizjmc, it is important to note that Jeff set the ROWCOUNT back to zero after he was finished. Don't forget to do this! Forgetting to do this will limit your result sets for the duration of that connection.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 7

Spot on, John...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

faizjmc SSC Eights! Points: 881 More actions · Answer 8

faizjmc

SSC Eights!

Points: 881

November 6, 2006 at 9:43 pm

#670221

10q John n Jeff