@@Rowcount in view???

Question

Post reply

@@Rowcount in view???

Ninja's_RGR'us

SSC Guru

Points: 294069

August 13, 2007 at 9:14 am

#93544

I was browsing the create view topic in the books online and I found this little "gem?" :

E. Use @@ROWCOUNT function in a view

This example uses the @@ROWCOUNT function as part of the view definition.

USE pubs

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS      WHERE TABLE_NAME = 'myview')

DROP VIEW myview

GO

CREATE VIEW myview

AS   SELECT au_lname, au_fname, @@ROWCOUNT AS bar

FROM authors

WHERE state = 'UT'GOSELECT * FROM myview

The question that comes to mind is : why the heck would you want to use that, ever??

I just can't seem to wrap my mind around this one!!

Viewing 15 posts - 1 through 14 (of 14 total)

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

bledu SSChampion Points: 12335 More actions · Answer 1

i can't understand its use either from that perspective.

Everything you can imagine is real.

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 2

ditto. Seems like a strange example. CAn you give a URL for this?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Books online / Create view / E

I'll try to find it on msdn...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Yup, even with the June 2007 version :

http://msdn2.microsoft.com/en-us/library/aa258253(SQL.80).aspx

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

Faster than COUNT(*)?

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Maybe...

So you'd run a select in a view, then select * from vwRowcount after that instead of SET @@ROWCOUNT??

If you run the view, you'll notice that @@rowcount is populated only AFTER the view has run. The first run will give 0 and the 2nd run will give 2. That's why I just can't see how usefull this can be... unless you want to propagate the rowcount from a view to another in a big process !?

This still leaves me somewhat puzzled!

bledu SSChampion Points: 12335 More actions · Answer 7

maybe Stevo can use his M$ connections to get an explanation eh!

Everything you can imagine is real.

Bob Fazio SSChampion Points: 10794 More actions · Answer 8

Wouldn't matter

@@ROWCOUNT will always refer to the previous select not the current one. So given that the name of the column is "BAR" perhaps it is where the person the wrote the view last was

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Heh... some of the best (and worst) ideas are spawned on cocktail napkins... this one happens to be one of the worst... if you run it more than once, it looks like it works, but it's really doing what Bob said... reporting on the previous query. Start a fresh window and it reports zero on the first run.

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Why do you think I can't figure out a usefull way to use that thing???

The only way I could see a good use would be to do a deffered cross-join :

1 - Query returns 5 rows...

2 - then return 5 duplicates of each row in the previous query in another part of the application.

That could act as a reporting trigger for a select in another view... but then again, that would have to be coded manually (2nd call to a view) so we're pretty much back to square one .

Of course I'm not sure I'd use views for this one, but that might be a usefull case. And I use might very loosely in this case .

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Heh... I once had a friend that tried to make bottles from broken glass. That didn't work out so well, either

--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)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 12

It is very easy to make broken glass from bottles, though, and a lot more fun.

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 13

More fun if there's a motivational picture on the bottle

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Like the one of the guy who invented that exemple ?! .