Numbers of rows returned from mquery

I am using the following code to find if a use exits in the table:
declare @.UserName char(30)
set @.UserName = (select fullname from udf_GetUserName(@.UserId))
SELECT @.@.ROWCOUNT
but that always returns 1. How do I solve that?
ThanksYou are getting the rowcount value for the SET variable statement, which
obviously always equal to 1. If you'd like to know how many full name values
exist for a given identifier, you might consider using COUNT(*) like:
SELECT COUNT(*) FROM tbl_valued_udf ;
or avoid the assignment and do:
SELECT col FROM tbl_valued_udf ;
SELECT @.@.ROWCOUNT ;
Alternatively to check the existence, you could do:
IF EXISTS ( SELECT * FROM tbl_valued_udf )
Anith|||You solve it by asking the correct query. :)
What you are asking SQL to return is the number of rows affected by the
previous statement. Since the previous SELECT always returns a single row,
you get a rowcount of 1. What you really should do is select the fullname
from the underlying user table (or from an abstraction such as a view or
procedure) where the userID = @.userID (supplied parameter). If you get an
empty result set, then the user doesn't exist.
It looks like someone is trying to write an abstraction layer for
programmers so they won't have to learn any SQL. That leads to some really
bad code.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Markgoldin" <Markgoldin@.discussions.microsoft.com> wrote in message
news:A494674C-147F-4009-8C40-99C163F6568D@.microsoft.com...
>I am using the following code to find if a use exits in the table:
> declare @.UserName char(30)
> set @.UserName = (select fullname from udf_GetUserName(@.UserId))
> SELECT @.@.ROWCOUNT
> but that always returns 1. How do I solve that?
> Thanks|||Why not just test IF IS NULL(@.UserName) instead?
Roy Harvey
Beacon Falls, CT
On Wed, 5 Apr 2006 12:47:01 -0700, Markgoldin
<Markgoldin@.discussions.microsoft.com> wrote:

>I am using the following code to find if a use exits in the table:
>declare @.UserName char(30)
>set @.UserName = (select fullname from udf_GetUserName(@.UserId))
>SELECT @.@.ROWCOUNT
>but that always returns 1. How do I solve that?
>Thanks

Numbering Rows with a Twist

OK,

Here is my challenge.

If I have a query that produces the following

ItemSold_On
A01-10-2004 8:03
A01-11-2004 10:05
A01-12-20041:37
A01-14-20047:16
B01-10-20049:37
B01-12-2004 11:42
B01-13-20049:37

But I need it to produce this instead

ItemSold_On Instance
A01-10-2004 8:031
A01-11-2004 10:052
A01-12-20041:373
A01-14-20047:164
B01-10-20049:371
B01-12-2004 11:422
B01-13-20049:373

So basically I need it to chronologically number the rows, but I need
the count to start over when the item changes.You haven't given us any information about your base table(s). I'll assume
the query result you posted represents an actual table that looks like this:

CREATE TABLE Sometable (item CHAR(1), sold_on DATETIME, PRIMARY KEY
(item,sold_on))

INSERT INTO Sometable VALUES ('A', '2004-01-10T08:03:00')
INSERT INTO Sometable VALUES ('A', '2004-01-11T10:05:00')
INSERT INTO Sometable VALUES ('A', '2004-01-12T01:37:00')
INSERT INTO Sometable VALUES ('A', '2004-01-14T07:16:00')
INSERT INTO Sometable VALUES ('B', '2004-01-10T09:37:00')
INSERT INTO Sometable VALUES ('B', '2004-01-12T11:42:00')
INSERT INTO Sometable VALUES ('B', '2004-01-13T09:37:00')

Here's the query. Depending on your actual data this may not work as
expected (it relies on the combination of (item, sold_on) being unique) or
there may be a better way.

SELECT S1.item, S1.sold_on, COUNT(*) AS instance
FROM Sometable AS S1
JOIN Sometable AS S2
ON S1.item = S2.item
AND S1.sold_on >= S2.sold_on
GROUP BY S1.item, S1.sold_on

Hope this helps.

--
David Portas
----
Please reply only to the newsgroup
--|||> But I need it to produce this instead
> ItemSold_On Instance
> A01-10-2004 8:031
> A01-11-2004 10:052
> A01-12-20041:373
> A01-14-20047:164
> B01-10-20049:371
> B01-12-2004 11:422
> B01-13-20049:373

This won't work until Yukon, but already works in Oracle and DB2...

select item
, sold_on
, row_number() over(partition by item
order by sold_on) as Instance
from your_table;

Christian.|||Thanks David,

I will give it a shot.

Actually the recordset you saw would be from a previous query but I at
least know enough to get your suggestion to work. Even if I have to
create a logical table to do it.

Thanks again,

David Meriwether

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

numbering rows of unordered table?

create table t (colA int, colB char(1), colC int)
insert into t(colB, colC) Values('C', 3)
insert into t(colB, colC) Values('C', 1)
insert into t(colB, colC) Values('C', 4)
insert into t(colB, colC) Values('C', 2)
insert into t(colB, colC) Values('A', 4)
insert into t(colB, colC) Values('A', 1)
insert into t(colB, colC) Values('A', 3)
insert into t(colB, colC) Values('A', 2)
insert into t(colB, colC) Values('B', 2)
insert into t(colB, colC) Values('B', 3)
insert into t(colB, colC) Values('B', 4)
insert into t(colB, colC) Values('B', 1)
so colA of table t contains all nulls right now.
Select * from t
NULL C 3
NULL C 1
NULL C 4
NULL C 2
NULL A 4
NULL A 1
NULL A 3
NULL A 2
NULL B 2
NULL B 3
NULL B 4
NULL B 1
I need to number each row of table t so it looks like this
Select * from t Order By colB, colC
1 A 1
2 A 2
3 A 3
4 A 4
5 B 1
6 B 2
7 B 3
8 B 4
9 C 1
10 C 2
11 C 3
12 C 4
In my actual app Table t already exists with colA = null
and colB and colC as above and thousands of rows. Thus,
to say
Update t set colA = 1 Where colB = 'A' And colC = '1'
Update t set colA = 2 Where colB = 'A' And colC = '2'
Update t set colA = 3 Where colB = 'A' And colC = '3'
...
Update t set colA = 9 Where colB = 'C' And colC = '1'
Update t set colA = 10 Where colB = 'C' And colC = '2'
...
is clearly is not the way to go. I humbly request if
someone could show me how to number the rows with T-sql
the correct way. My problem is that I don't know how to
increment the seed number and how to apply it to the
desired order. I am thinking a while loop, but what flag
to use to stop the loop? How to order the rows?
Thanks,
RonTry,
select
count(*) as colA,
a.colB,
a.colC
from
t as a
inner join
t as b
on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
group by
a.colB,
a.colC
order by
1
go
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Ron" wrote:

> create table t (colA int, colB char(1), colC int)
> insert into t(colB, colC) Values('C', 3)
> insert into t(colB, colC) Values('C', 1)
> insert into t(colB, colC) Values('C', 4)
> insert into t(colB, colC) Values('C', 2)
> insert into t(colB, colC) Values('A', 4)
> insert into t(colB, colC) Values('A', 1)
> insert into t(colB, colC) Values('A', 3)
> insert into t(colB, colC) Values('A', 2)
> insert into t(colB, colC) Values('B', 2)
> insert into t(colB, colC) Values('B', 3)
> insert into t(colB, colC) Values('B', 4)
> insert into t(colB, colC) Values('B', 1)
> so colA of table t contains all nulls right now.
> Select * from t
> NULL C 3
> NULL C 1
> NULL C 4
> NULL C 2
> NULL A 4
> NULL A 1
> NULL A 3
> NULL A 2
> NULL B 2
> NULL B 3
> NULL B 4
> NULL B 1
> I need to number each row of table t so it looks like this
> Select * from t Order By colB, colC
> 1 A 1
> 2 A 2
> 3 A 3
> 4 A 4
> 5 B 1
> 6 B 2
> 7 B 3
> 8 B 4
> 9 C 1
> 10 C 2
> 11 C 3
> 12 C 4
> In my actual app Table t already exists with colA = null
> and colB and colC as above and thousands of rows. Thus,
> to say
> Update t set colA = 1 Where colB = 'A' And colC = '1'
> Update t set colA = 2 Where colB = 'A' And colC = '2'
> Update t set colA = 3 Where colB = 'A' And colC = '3'
> ...
> Update t set colA = 9 Where colB = 'C' And colC = '1'
> Update t set colA = 10 Where colB = 'C' And colC = '2'
> ...
> is clearly is not the way to go. I humbly request if
> someone could show me how to number the rows with T-sql
> the correct way. My problem is that I don't know how to
> increment the seed number and how to apply it to the
> desired order. I am thinking a while loop, but what flag
> to use to stop the loop? How to order the rows?
> Thanks,
> Ron
>|||here is the update.
update
t
set
colA = (select count(*) from t as a where t.colB + ltrim(t.colC) >= a.colB
+ ltrim(a.colC))
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> count(*) as colA,
> a.colB,
> a.colC
> from
> t as a
> inner join
> t as b
> on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
> group by
> a.colB,
> a.colC
> order by
> 1
> go
> How to dynamically number rows in a SELECT Statement
> http://support.microsoft.com/defaul...kb;en-us;186133
>
> AMB
>
> "Ron" wrote:
>|||Thanks very much for your reply. I guess the trick was in
the self join. I took this one step further and performed
an update (as I need to hardcode these numbers):
update t set t.colA = t2.colA
From t Join
(select count(*) as colA, a.colB, a.colC from t as a inner
join t as b on a.colB + ltrim(a.colC) >= b.colB + ltrim
(b.colC) group by a.colB, a.colC) t2
on t.colB = t2.colB and t.colC = t2.colC
Question: someone advised me that using joins in an
update statement is not correct. But this Update
statement accomplished what I needed. Any comments
appreciated.
Thanks again for your help.
Ron

>--Original Message--
>Try,
>select
> count(*) as colA,
> a.colB,
> a.colC
>from
> t as a
> inner join
> t as b
> on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
>group by
> a.colB,
> a.colC
>order by
> 1
>go
>How to dynamically number rows in a SELECT Statement
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;186133
>
>AMB|||Read my last post.
AMB
"Ron" wrote:

> Thanks very much for your reply. I guess the trick was in
> the self join. I took this one step further and performed
> an update (as I need to hardcode these numbers):
> update t set t.colA = t2.colA
> From t Join
> (select count(*) as colA, a.colB, a.colC from t as a inner
> join t as b on a.colB + ltrim(a.colC) >= b.colB + ltrim
> (b.colC) group by a.colB, a.colC) t2
> on t.colB = t2.colB and t.colC = t2.colC
> Question: someone advised me that using joins in an
> update statement is not correct. But this Update
> statement accomplished what I needed. Any comments
> appreciated.
> Thanks again for your help.
> Ron
>
> us;186133
>|||Thanks again for this correction.

>--Original Message--
>here is the update.
>update
> t
>set
> colA = (select count(*) from t as a where t.colB +
ltrim(t.colC) >= a.colB
>+ ltrim(a.colC))
>go
>
>AMB
>"Alejandro Mesa" wrote:
>
us;186133
this
null
Thus,
sql
to
flag
>.
>

numbering rows

How do i number the rows in the table? My data does not have any unique
values if that helpsThere is a RowNumber(<scope>) function, where <scope> is a string that
identifies a data region, dataset or grouping if you need that context. For
just a simple running row total, use RowNumber(Nothing). You can also use
it for visual effects, and the most frequent example of this is to create
"green bar" reports by setting the background colour of a table row with the
expression:
=iif(RowNumber(Nothing) Mod 2, "Green", "White")
Cheers, Mark
"Marvin" <Marvin@.discussions.microsoft.com> wrote in message
news:25E67CAB-76C0-450A-B4DE-87788DD19E80@.microsoft.com...
> How do i number the rows in the table? My data does not have any unique
> values if that helps

Numbering in SQL

I have table with 10-20 rows with field P6 which is empty. I want to
update numbers to P6 starting 1 and increasing by 1. I suppose it is
done by triggers but I don't know how to do that. Help :-)is this as 1 off or every time you INSERT a record?

--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

<lemes_m@.yahoo.com> wrote in message
news:1148037597.406952.273140@.j33g2000cwa.googlegr oups.com...
> I have table with 10-20 rows with field P6 which is empty. I want to
> update numbers to P6 starting 1 and increasing by 1. I suppose it is
> done by triggers but I don't know how to do that. Help :-)|||Make the column a identity value, this way it is incremented with every
insert:

int IDENTITY (1, 1)|||lemes_m@.yahoo.com (lemes_m@.yahoo.com) writes:
> I have table with 10-20 rows with field P6 which is empty. I want to
> update numbers to P6 starting 1 and increasing by 1. I suppose it is
> done by triggers but I don't know how to do that. Help :-)

Without further knowledge about the table it is difficult to give
advice. And if the rest of the data is not unique, it's getting sort
of ugly.

For a one-off you could do:

DECLARE @.i int
SELECT @.i = 1
-- SET ROWCOUNT 1 Use this on SQL 2000.
WHILE EXISTS (SELECT * FROM tbl WHERE P6 IS NULL)
BEGIN
UPDATE /* TOP(1) */ tbl -- Remove comment for SQL 2005.
SET P6 = @.i

SELECT @.i = @.i + 1
END
-- SET ROWCOUNT 0 again, for SQL 2000.

But I would not like to see this code in a trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> have table with 10-20 rows with field [sic] P6 which is empty [sic]. <<

You have NEVER written SQL before, have you? Columsn are not fields
and we have NULLs, not the empty spreadsheet cells you assume. TOTALLY
WRONG MINDSET!

>> I want to update numbers to P6 starting 1 and increasing by 1. <<

That is a SEQUENTAL MAGNETIC TAPE FILE and you are tryignto write
1950's code in SQL! It has northing whatsoever to do with RDBMS.
Tables have no ordering by definition. This is soooooo wrong ...|||Geeesh!!! You can insert a set at a time, so adding one to a previous
value makes no sense. Doesn't anyone go to RDBMS classes any more?|||>> Make the column a identity value, this way it is incremented with every insert: <<

Always go for the proprietary and most non-relational kludge? LET'S
FINBD OUT WHAT HE IS REALLY TRYIGN TO DO BEFORE WE POST ANYTHING ELSE.

Okay, lemes_m@.yahoo.com , why do you want to destroy the relational
model? What is your business goal?|||Hi Lemes,

Check out http://blogs.msdn.com/sqlcat/archiv.../10/572848.aspx it has
good examples of how to do this.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

<lemes_m@.yahoo.com> wrote in message
news:1148037597.406952.273140@.j33g2000cwa.googlegr oups.com...
>I have table with 10-20 rows with field P6 which is empty. I want to
> update numbers to P6 starting 1 and increasing by 1. I suppose it is
> done by triggers but I don't know how to do that. Help :-)

Numbering groups of rows?

create table t (colA int, colB char(1), colC int)
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
update t set colc =
(select count(*) from t as a where t.colb >= a.colb)
yields
NULL C 12
NULL C 12
NULL C 12
NULL C 12
NULL A 4
NULL A 4
NULL A 4
NULL A 4
NULL B 8
NULL B 8
NULL B 8
NULL B 8
how can I make it yield
NULL C 3
NULL C 3
NULL C 3
NULL C 3
NULL A 1
NULL A 1
NULL A 1
NULL A 1
NULL B 2
NULL B 2
NULL B 2
NULL B 2
Thanks,
Ronupdate t set colc =
(select count(DISTINCT a.colb) from t as a where t.colb >= a.colb)
Jacco Schalkwijk
SQL Server MVP
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:01b401c50fb9$5a806ee0$a501280a@.phx.gbl...
> create table t (colA int, colB char(1), colC int)
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> update t set colc =
> (select count(*) from t as a where t.colb >= a.colb)
> yields
> NULL C 12
> NULL C 12
> NULL C 12
> NULL C 12
> NULL A 4
> NULL A 4
> NULL A 4
> NULL A 4
> NULL B 8
> NULL B 8
> NULL B 8
> NULL B 8
> how can I make it yield
> NULL C 3
> NULL C 3
> NULL C 3
> NULL C 3
> NULL A 1
> NULL A 1
> NULL A 1
> NULL A 1
> NULL B 2
> NULL B 2
> NULL B 2
> NULL B 2
> Thanks,
> Ron
>|||On Thu, 10 Feb 2005 13:42:01 -0800, Ron wrote:

>update t set colc =
>(select count(*) from t as a where t.colb >= a.colb)
>yields
(snip)
>how can I make it yield
(snip)
Hi Ron,
Better not to store this information at all - you'll find yourself
constantly fighting to keep the rankingf column current after each
modification to the underlying data. It's better to drop the column from
the table and create a view to calculate it.
If you MUST do it in an update, try
update t set colc =
(select count(distinct a.colb) from t as a where t.colb >= a.colb)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks very much. That worked well. But I also tried
using this in a select statement as follows for ranking
the values A, B, C
select a.colB, count(distinct a.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
yielded this ranking
A 1
B 1
C 1
without the Distinct keyword I get this ranking
A 16
B 32
C 48
But I would like to get a ranking as follows
A 1
B 2
C 3
I ask this because I am trying to understand the sql logic
to achieve these results. Hopefully, after I do enough of
these kinds of queries I will get the idea how they work.
May I ask how I could achieve the ranking from result3?
Thanks again,
Ron

>--Original Message--
> update t set colc =
>(select count(DISTINCT a.colb) from t as a where t.colb
>= a.colb)
>
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01b401c50fb9$5a806ee0$a501280a@.phx.gbl...
>
>.
>|||Ron,
Try count(distinct b.colb) instead of count(distinct a.colb). I suspect
that's what you had in mind.
Steve Kass
Drew University
Ron wrote:
>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
>yielded this ranking
>A 1
>B 1
>C 1
>without the Distinct keyword I get this ranking
>A 16
>B 32
>C 48
>But I would like to get a ranking as follows
>A 1
>B 2
>C 3
>I ask this because I am trying to understand the sql logic
>to achieve these results. Hopefully, after I do enough of
>these kinds of queries I will get the idea how they work.
>May I ask how I could achieve the ranking from result3?
>Thanks again,
>Ron
>
>
>message
>|||On Thu, 10 Feb 2005 14:21:58 -0800, Ron wrote:

>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
Hi Ron,
Try this one instead:
select a.colB, count(distinct b.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
(Note: only one letter weas changed!!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks very much. I am also trying to figure out how to
rank A, B, C
select a.colB, count(distinct a.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
yielded this ranking
A 1
B 1
C 1
without the Distinct keyword I get this ranking
A 16
B 32
C 48
But I would like to get a ranking as follows
A 1
B 2
C 3
May I ask how I could achieve the ranking from result3?
This way, as you say, I don't really store the ranks, just
retrieve them dynamically.
Thanks again,
Ron

>--Original Message--
>On Thu, 10 Feb 2005 13:42:01 -0800, Ron wrote:
>
>(snip)
>(snip)
>Hi Ron,
>Better not to store this information at all - you'll find
yourself
>constantly fighting to keep the rankingf column current
after each
>modification to the underlying data. It's better to drop
the column from
>the table and create a view to calculate it.
>If you MUST do it in an update, try
>update t set colc =
>(select count(distinct a.colb) from t as a where t.colb
>= a.colb)
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||Thanks all. I am sort of starting to get the idea. But I
wonder if I could belabor this thing one more notch:
Instead of using distinct is it possible to plant a group
by query in there? Pseudocode here:
select a.colB, count(select a.colb from a group by a.colb)
as colC from t as a inner join t as b
on a.colB >= b.colB group by a.colB
Again, I just ask because I don't really know all the
rules for t-sql, let alone the tricks. I am guessing that
t-sql does not allow Selects inside of Count(..)
Thanks again,
Ron

>--Original Message--
>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
>yielded this ranking
>A 1
>B 1
>C 1
>without the Distinct keyword I get this ranking
>A 16
>B 32
>C 48
>But I would like to get a ranking as follows
>A 1
>B 2
>C 3
>I ask this because I am trying to understand the sql
logic
>to achieve these results. Hopefully, after I do enough
of
>these kinds of queries I will get the idea how they
work.
>May I ask how I could achieve the ranking from result3?
>Thanks again,
>Ron
>
>message
>.
>|||On Thu, 10 Feb 2005 14:45:11 -0800, Ron wrote:

>Thanks all. I am sort of starting to get the idea. But I
>wonder if I could belabor this thing one more notch:
>Instead of using distinct is it possible to plant a group
>by query in there? Pseudocode here:
>select a.colB, count(select a.colb from a group by a.colb)
>as colC from t as a inner join t as b
>on a.colB >= b.colB group by a.colB
Hi Ron,
This code won't work. I'm sure there is some way to do this with a group
by in the subquery, but it's not trivial and it'll be more complex than
the version with DISTINCT that I suggested.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Instead of using distinct is it possible to plant a group by query
in there? <<
This will use a GROUP BY and get you a bit more information in the VIEW
aggregate functions. I am not sure that there is any advantage. .
CREATE TABLE Foobar (letter CHAR(1) NOT NULL);
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
CREATE VIEW FoobarReport (letter, occurs, place)
AS
SELECT F1.letter, COUNT(*),
(SELECT COUNT (DISTINCT F2.letter)
FROM Foobar AS F2
WHERE F2.letter <= F1.letter)
FROM Foobar AS F1
GROUP BY F1.letter;

Number/enumerate rows in a table from scratch?

I have a table with 10 rows - one int column and 3 varchar cols. There is n
o
unique data. How can I number/enumerate the rows from say 1 to 10 with Tsq
l?
create table tbl1(
RowNum int,
fld1 varchar(5),
fld2 varchar(5),
fld3 varchar(5))
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Thanks,
RichConsider making the RowNum column an identity.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DAD8E08B-207D-4EBF-BF4F-A8273B1D3536@.microsoft.com...
I have a table with 10 rows - one int column and 3 varchar cols. There is
no
unique data. How can I number/enumerate the rows from say 1 to 10 with
Tsql?
create table tbl1(
RowNum int,
fld1 varchar(5),
fld2 varchar(5),
fld3 varchar(5))
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
Thanks,
Rich|||Thanks. I thought about that. But I was just wondering - based on my
criteria, if it would be possible to enumerate a table with Tsql - like mayb
e
using a cursor? For example, in VBA you could use DAO code to enumerate a
table:
Set RS = DB.OpenRecordset("tbl1")
Do While Not RS.EOF
RS.Edit
RS!RowNum = i
RS.Update
i = i + 1
RS.MoveNext
Loop
This is kind of like a cursor except that a cursor seems to require
something unique. I was thinking in pseudocode Update tbl1 set top 1 Rownum
= 1. Then use a self join and set next row to max(Rownum) + 1. But how do
I
determine the next row with Tsql in my scenario?
"Tom Moreau" wrote:

> Consider making the RowNum column an identity.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DAD8E08B-207D-4EBF-BF4F-A8273B1D3536@.microsoft.com...
> I have a table with 10 rows - one int column and 3 varchar cols. There is
> no
> unique data. How can I number/enumerate the rows from say 1 to 10 with
> Tsql?
> create table tbl1(
> RowNum int,
> fld1 varchar(5),
> fld2 varchar(5),
> fld3 varchar(5))
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Thanks,
> Rich
>|||If this is a useful table in your system, you must remove the duplicates and
explicitly assign a primary key for data integrity purposes. For details
refer to:
http://support.microsoft.com/defaul...b;EN-US;q139444
Once you have unique rows, ranking becomes much simpler. For instance see:
http://support.microsoft.com/defaul...b;EN-US;q186133
Though it makes little sense, with you current table schema with no keys,
you can simply add the "pseudo rank" using an identity column.
Alternatively, you can use a rank like:
ALTER TABLE tbl1 ADD idCol INT NOT NULL IDENTITY
GO
SELECT ( SELECT COUNT( * )
FROM tbl1 t2
WHERE t2.fld1 = t1.fld1
AND t2.fld2 = t1.fld2
AND t2.fld3 = t1.fld3
AND t2.idCol <= t1.idCol ),
t1.fld1, t1.fld2, t1.fld3
FROM tbl1 t1 ;
GO
ALTER TABLE tbl1 DROP COLUMN idCol
GO
SELECT * FROM tbl1
Another approach is to use a table of sequentially incrementing numbers. You
can create one like :
SELECT IDENTITY( INT ) "n" INTO Nbrs FROM sysobjects s1, sysobjects s2 ;
Now you can do:
SELECT Nbrs.n, fld1, fld2, fld3
FROM ( SELECT fld1, fld2, fld3, COUNT( * )
FROM tbl1
GROUP BY fld1, fld2, fld3 ) D ( fld1, fld2, fld3, n )
INNER JOIN Nbrs
ON D.n >= Nbrs.n ;
Another way of doing this would be like:
SELECT n, fld1, fld2, fld3
FROM tbl1, Nbrs
GROUP BY fld1, fld2, fld3, n
HAVING n <= COUNT(*) ;
Anith|||Without something to provide uniqueness, you're stuck.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:57F251E0-A033-4047-9502-A9A5415814B2@.microsoft.com...
Thanks. I thought about that. But I was just wondering - based on my
criteria, if it would be possible to enumerate a table with Tsql - like
maybe
using a cursor? For example, in VBA you could use DAO code to enumerate a
table:
Set RS = DB.OpenRecordset("tbl1")
Do While Not RS.EOF
RS.Edit
RS!RowNum = i
RS.Update
i = i + 1
RS.MoveNext
Loop
This is kind of like a cursor except that a cursor seems to require
something unique. I was thinking in pseudocode Update tbl1 set top 1 Rownum
= 1. Then use a self join and set next row to max(Rownum) + 1. But how do
I
determine the next row with Tsql in my scenario?
"Tom Moreau" wrote:

> Consider making the RowNum column an identity.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DAD8E08B-207D-4EBF-BF4F-A8273B1D3536@.microsoft.com...
> I have a table with 10 rows - one int column and 3 varchar cols. There is
> no
> unique data. How can I number/enumerate the rows from say 1 to 10 with
> Tsql?
> create table tbl1(
> RowNum int,
> fld1 varchar(5),
> fld2 varchar(5),
> fld3 varchar(5))
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Thanks,
> Rich
>|||Thank you all for your replies and suggestions. These have really helped me
to understand about uniqueness and numbering. Actually, I sort of lost sigh
t
of why I was pursuing this, but I realized that with the VBA DAO each row in
an MS Access table, for example has a unique binary row identifier which is
now exposed for usage. But DAO uses it to movenext. So I can see that ther
e
is no way to movenext without some unique Identifier in a Sql Table.
Thanks all for your help.
Rich
"Rich" wrote:

> I have a table with 10 rows - one int column and 3 varchar cols. There is
no
> unique data. How can I number/enumerate the rows from say 1 to 10 with T
sql?
> create table tbl1(
> RowNum int,
> fld1 varchar(5),
> fld2 varchar(5),
> fld3 varchar(5))
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Insert into tbl1 Values(null, 'abc', 'def', 'ghi')
> Thanks,
> Rich

number rows

I have wrote a query that returns product details for all orders that are at stage 6. Is there a way to number the product lines in each order no?!

Eg.
Order 1
Product 1 - 1
Product 2 - 2
Product 3 - 3

--counter restarts
Order 2
Product 1 - 1
Product 2 -2

thanks?If you are using SQL 2005, you could look into using the ROW_NUMBER function with the PARTITION BY option.|||Or you can just let the front end do it..or insert the rows into a temp table with an identity column then select from that

Number of Updated Rows

I want to get the number of rows updated by an update statement in a stored procedure. Is there a way to do this?

Example:

Declare @.NumRow int;

set @.NumRow = udpate table set Name = 'John Doe' where ID = 123456789;

print @.NumRow;

The above doesn't work.

use the @.@.ROWCOUNT internal; something like:

declare @.updateRows integer

UPDATE yourTable
SET ...
WHERE ...
SET @.updateRows = @.@.ROWCOUNT

Dave

|||

Oh yea, a hidden gotcha:

You need to be careful and make sure that the @.@.ROWCOUNT internal is used IMMEDIATELY after the update statement so that its value does not change. You may get surprised if you have any steps between your update statement and where you used @.@.ROWCOUNT.

Dave

|||Yeah, @.@.rowcount is very good that way.

Something else you might want to be aware of is that you can output the rows from an update query. This gives you the chance to do all kinds of extra analysis on what's changed - not just the number of rows, but all kinds of other details too.

Have a look through http://msdn2.microsoft.com/en-us/ms177564.aspx to see some of the things you can get up to.

Robsql

Number of Rows returned by sp

Is it possible to get the number of Rows returned by a stored procedure in a profiler ?
if yes, what parameters shud I be looking for?
THanks
HarshalDo you mean like:

USE Northwind
GO

CREATE PROC mySproc99 @.rs int OUTPUT
AS
SELECT * FROM Orders
SELECT @.rs = @.@.ROWCOUNT
GO

DECLARE @.rs int

EXEC mySproc99 @.rs OUTPUT

SELECT @.rs
GO

DROP PROC mySproc99
GO|||no.
the sps are not to be changed.
its like

create procedure test
as
begin
select * from table
end

suppose this is the existing sp which is being used by a application
I want to put the profiler to get the number of rows which are being selected by this sp.
since its the prod db I cant change the sp.|||You may want to look into sp_trace_generateevent and related topics, but I think you'd still need to alter your procedures.|||From books online...

Use the SP:StmtCompleted event, and trace the Integer Data.

It is a bit difficult to find in BOL, so I bookmarked it, for myself. Try searching on "Monitoring with SQL Profiler Event Categories" The quoted string, will get you the desired result|||I can find the topic, and I can find a table that shows that the integer counter returns something for a stored procedure's StmtCompleted event, but darned if I can find anywhere that it explicitly says what that integer is!

Good sleuthing!

-PatP|||Pat: There should be two links on the page. Following "Stored Procedures Data Columns" should bring you to a short description of all the data elements.|||Hey thank you guys !!
I got the required data by importing the required data to my box and altered the proc and ran it. :cool: Ok I agree this is not a professional way to do things but it was a show stopper bug in the system so had to find a way.
I'll check the profiler events that rdjabarov and MCrowley has suggested.

Thanks again.

Regards,
Harshal.

number of rows on a page

Hi,
could any one tell me please, how to find out the number of rows on a page?
I need it for some like:
if rows-available < 6
do some
tia
ronHow about using running totals field which u can find in the "Field explorer"?:wave:|||thank u CrystalBabysteps but I can't figure out how many rows left with "field explorer" (or I don't know how to do)
if I run a report and after some records I like to know if 6 rows still left, if not make page breaks
ron|||what iam attempting to tell you is, make a running totals field and place it in the required section say Details. Then go open the report->section expert (assuming ur using CR9 the menu may be lil diff in other vers). There u will find a check box "New page after" with a small button having caption x-2. click it and write in the space provided

if {#RTotal0}>6 then true else false;
Note {#RTotal0} is name you might have given to the Running total
press Alt+C to see there are any errors.else save it and exit.
I believe this should solve ur problem

How to make Running Totals field?
See to the left of the screen you should find "Field Explorer". If u dont see it go to View->Field Explorer. In the FE u should find Running Totals Fields, Rt click it and select New... select the field which would determine the rows in a page. Give a name to Running Total Name, do the other selections as required, then OK and exit

Number of rows of all the tables in the database

Is there a way to get a list of all the table and the
number of rows it contains?
Thanks
Anna> Is there a way to get a list of all the table and the
> number of rows it contains?
http://www.aspfaq.com/2428
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Anna,
> Is there a way to get a list of all the table and the
> number of rows it contains?
You can also open a cursor and get the counts of each table in a
loop. This procedure allows you to do a bit of filtering of the
tables.
use Northwind
go
create procedure get_rowcounts (
@.table sysname = '%'
) as
declare @.sql varchar(1000)
create table #Tmp (table_name sysname, row_count int)
declare csrTable cursor local fast_forward for
select table_name from information_schema.tables
where table_name like @.table
and table_type = 'BASE TABLE'
order by table_name
open csrTable
fetch next from csrTable into @.table
while (@.@.fetch_status = 0) begin
set @.Sql = 'select ''' + @.table +
''', count(*) from [' + @.table + ']'
-- print @.sql
insert #tmp exec (@.sql)
fetch next from csrTable into @.table
end
close csrTable
deallocate csrTable
select row_count, table_name from #tmp
go
exec get_rowcounts 'Customers'
exec get_rowcounts 'Order%'
exec get_rowcounts
go
drop procedure get_rowcounts
-- Linda|||Thanks
>--Original Message--
>Anna,
>> Is there a way to get a list of all the table and the
>> number of rows it contains?
>You can also open a cursor and get the counts of each
table in a
>loop. This procedure allows you to do a bit of filtering
of the
>tables.
>
>use Northwind
>go
>create procedure get_rowcounts (
> @.table sysname = '%'
>) as
>declare @.sql varchar(1000)
>create table #Tmp (table_name sysname, row_count int)
>declare csrTable cursor local fast_forward for
>select table_name from information_schema.tables
>where table_name like @.table
>and table_type = 'BASE TABLE'
>order by table_name
>open csrTable
>fetch next from csrTable into @.table
>while (@.@.fetch_status = 0) begin
> set @.Sql = 'select ''' + @.table +
> ''', count(*) from [' + @.table + ']'
>-- print @.sql
> insert #tmp exec (@.sql)
> fetch next from csrTable into @.table
>end
>close csrTable
>deallocate csrTable
>select row_count, table_name from #tmp
>go
>exec get_rowcounts 'Customers'
>exec get_rowcounts 'Order%'
>exec get_rowcounts
>go
>drop procedure get_rowcounts
>
>-- Linda
>
>.
>|||Hi, you can try to use select count(*) from tablename.
Yanling
>--Original Message--
>Is there a way to get a list of all the table and the
>number of rows it contains?
>Thanks
>Anna
>.
>sql

Number of rows in the Excel sheet exceeded the limit of 65536 rows

Hi,
I got this message from the export to xls in reporting services.
Is there a way to prevent this? Can I set some property on the report to use
a new sheet on a group for example?
Thanks
BartOn Mar 15, 5:37 am, Bart <bart...@.community.nospam> wrote:
> Hi,
> I got this message from the export to xls in reporting services.
> Is there a way to prevent this? Can I set some property on the report to use
> a new sheet on a group for example?
> Thanks
> Bart
There are a couple of ways to handle this. One way would be to select
the properties on the report control you are using (i.e., table, list,
etc) and select 'Insert a page break after this table' (ie). Another
way to handle this is to select 'Page break at end' as part of the
control's grouping. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Hi Enrique,
Thanks for your help.
It seems that a pagebreak is working on groups.
Bart
"EMartinez" wrote:
> On Mar 15, 5:37 am, Bart <bart...@.community.nospam> wrote:
> > Hi,
> >
> > I got this message from the export to xls in reporting services.
> > Is there a way to prevent this? Can I set some property on the report to use
> > a new sheet on a group for example?
> >
> > Thanks
> > Bart
> There are a couple of ways to handle this. One way would be to select
> the properties on the report control you are using (i.e., table, list,
> etc) and select 'Insert a page break after this table' (ie). Another
> way to handle this is to select 'Page break at end' as part of the
> control's grouping. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 15, 9:18 am, Bart <bart...@.community.nospam> wrote:
> Hi Enrique,
> Thanks for your help.
> It seems that a pagebreak is working on groups.
> Bart
> "EMartinez" wrote:
> > On Mar 15, 5:37 am, Bart <bart...@.community.nospam> wrote:
> > > Hi,
> > > I got this message from the export to xls in reporting services.
> > > Is there a way to prevent this? Can I set some property on the report to use
> > > a new sheet on a group for example?
> > > Thanks
> > > Bart
> > There are a couple of ways to handle this. One way would be to select
> > the properties on the report control you are using (i.e., table, list,
> > etc) and select 'Insert a page break after this table' (ie). Another
> > way to handle this is to select 'Page break at end' as part of the
> > control's grouping. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
You're welcome. Glad it worked out.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I'm sorry, but forgot 'not' in my previous message.
A pagebreak doesn't work on groups.
Thanks
Bart
"EMartinez" wrote:
> On Mar 15, 9:18 am, Bart <bart...@.community.nospam> wrote:
> > Hi Enrique,
> >
> > Thanks for your help.
> > It seems that a pagebreak is working on groups.
> >
> > Bart
> >
> > "EMartinez" wrote:
> > > On Mar 15, 5:37 am, Bart <bart...@.community.nospam> wrote:
> > > > Hi,
> >
> > > > I got this message from the export to xls in reporting services.
> > > > Is there a way to prevent this? Can I set some property on the report to use
> > > > a new sheet on a group for example?
> >
> > > > Thanks
> > > > Bart
> >
> > > There are a couple of ways to handle this. One way would be to select
> > > the properties on the report control you are using (i.e., table, list,
> > > etc) and select 'Insert a page break after this table' (ie). Another
> > > way to handle this is to select 'Page break at end' as part of the
> > > control's grouping. Hope this helps.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
>
> You're welcome. Glad it worked out.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Hello Bart,
Here is the limitation of the Excel render in reporting services:
Excel Rendering Limitations
http://msdn2.microsoft.com/en-us/library/ms156418.aspx
Excel 2007 did extend the limitation and you may follow this article:
http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx
So my suggestion is that you could try to install the latest services pack
and render to excel 2007.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

number of rows in catalog

Hi
Is there a system query that will tell me the number of rows in a given
catalog?
Cheers
James
> Is there a system query that will tell me the number of rows in a given
> catalog?
Here is an easy way:
EXEC sp_MSforeachtable 'sp_spaceused ''?'', ''true'''
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
|||This will return a good estimate of the number of rows in each table:
SELECT A.name, B.rows
FROM sysobjects A
JOIN sysindexes B ON A.ID = B.ID
WHERE A.type = 'U'
AND B.INDID < 2
ORDER BY A.Name
It is important to know that this is an estimate. The numbers are usually
pretty close. If you find that they are off you may have to issue several
DBCC statements (such as updateusage).
Keith
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%23mC3OeQsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>
|||I was actually after the number of rows in the full text index catalog.
Any clues?
Cheers
James
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%23hU7YjRsEHA.1248@.TK2MSFTNGP10.phx.gbl...
> Here is an easy way:
> EXEC sp_MSforeachtable 'sp_spaceused ''?'', ''true'''
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
>
>
|||to get an accurate number for each table you would
select count(*) from table
For an estimate you might
select name, rowcnt from sysindexes where id > 100 and indid in (0,1)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%23mC3OeQsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>
|||catalogs don't contain words per se, but rather unique words. Use this
query to get an idea of the number of unique words
select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:eqFnnxRsEHA.1988@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I was actually after the number of rows in the full text index catalog.
> Any clues?
> Cheers
> James
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
> message news:%23hU7YjRsEHA.1248@.TK2MSFTNGP10.phx.gbl...
given
>
|||James,
You can use the following metadata Full Text Search (FTS) queries to obtain
info from the FT Catalog:
select FulltextCatalogProperty('<FT_Catalog>', 'UniqueKeyCount') -- Number
of unique words
select FullTextCatalogProperty('<FT_Catalog>', 'itemcount') -- row count + 1
select FullTextCatalogProperty('<FT_Catalog>', 'indexsize') -- Size of the
full-text index
See BOL title FULLTEXTCATALOGPROPERTY for more info.
Thanks,
John
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:#mC3OeQsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>
|||Hi James,
I think this will get you what you want ...
select FulltextCatalogProperty('CatalogName', 'ItemCount')
"James Brett" wrote:

> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>
>
sql

number of rows in catalog

Hi
Is there a system query that will tell me the number of rows in a given
catalog?
Cheers
James
> Is there a system query that will tell me the number of rows in a given
> catalog?
Here is an easy way:
EXEC sp_MSforeachtable 'sp_spaceused ''?'', ''true'''
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
|||This will return a good estimate of the number of rows in each table:
SELECT A.name, B.rows
FROM sysobjects A
JOIN sysindexes B ON A.ID = B.ID
WHERE A.type = 'U'
AND B.INDID < 2
ORDER BY A.Name
It is important to know that this is an estimate. The numbers are usually
pretty close. If you find that they are off you may have to issue several
DBCC statements (such as updateusage).
Keith
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%23mC3OeQsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>
|||I was actually after the number of rows in the full text index catalog.
Any clues?
Cheers
James
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:%23hU7YjRsEHA.1248@.TK2MSFTNGP10.phx.gbl...
> Here is an easy way:
> EXEC sp_MSforeachtable 'sp_spaceused ''?'', ''true'''
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
>
>
|||to get an accurate number for each table you would
select count(*) from table
For an estimate you might
select name, rowcnt from sysindexes where id > 100 and indid in (0,1)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%23mC3OeQsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>
|||catalogs don't contain words per se, but rather unique words. Use this
query to get an idea of the number of unique words
select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:eqFnnxRsEHA.1988@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I was actually after the number of rows in the full text index catalog.
> Any clues?
> Cheers
> James
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
> message news:%23hU7YjRsEHA.1248@.TK2MSFTNGP10.phx.gbl...
given
>
|||James,
You can use the following metadata Full Text Search (FTS) queries to obtain
info from the FT Catalog:
select FulltextCatalogProperty('<FT_Catalog>', 'UniqueKeyCount') -- Number
of unique words
select FullTextCatalogProperty('<FT_Catalog>', 'itemcount') -- row count + 1
select FullTextCatalogProperty('<FT_Catalog>', 'indexsize') -- Size of the
full-text index
See BOL title FULLTEXTCATALOGPROPERTY for more info.
Thanks,
John
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:#mC3OeQsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>
|||Hi James,
I think this will get you what you want ...
select FulltextCatalogProperty('CatalogName', 'ItemCount')
"James Brett" wrote:

> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>
>

number of rows in catalog

Hi
Is there a system query that will tell me the number of rows in a given
catalog?
Cheers
James> Is there a system query that will tell me the number of rows in a given
> catalog?
Here is an easy way:
EXEC sp_MSforeachtable 'sp_spaceused ''?'', ''true'''
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||This will return a good estimate of the number of rows in each table:
SELECT A.name, B.rows
FROM sysobjects A
JOIN sysindexes B ON A.ID = B.ID
WHERE A.type = 'U'
AND B.INDID < 2
ORDER BY A.Name
It is important to know that this is an estimate. The numbers are usually
pretty close. If you find that they are off you may have to issue several
DBCC statements (such as updateusage).
Keith
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%23mC3OeQsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>|||I was actually after the number of rows in the full text index catalog.
Any clues?
Cheers
James
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23hU7YjRsEHA.1248@.TK2MSFTNGP10.phx.gbl...
> Here is an easy way:
> EXEC sp_MSforeachtable 'sp_spaceused ''?'', ''true'''
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
>
>|||to get an accurate number for each table you would
select count(*) from table
For an estimate you might
select name, rowcnt from sysindexes where id > 100 and indid in (0,1)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%23mC3OeQsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>|||catalogs don't contain words per se, but rather unique words. Use this
query to get an idea of the number of unique words
select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:eqFnnxRsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> I was actually after the number of rows in the full text index catalog.
> Any clues?
> Cheers
> James
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:%23hU7YjRsEHA.1248@.TK2MSFTNGP10.phx.gbl...
given[vbcol=seagreen]
>|||James,
You can use the following metadata Full Text Search (FTS) queries to obtain
info from the FT Catalog:
select FulltextCatalogProperty('<FT_Catalog>', 'UniqueKeyCount') -- Number
of unique words
select FullTextCatalogProperty('<FT_Catalog>', 'itemcount') -- row count + 1
select FullTextCatalogProperty('<FT_Catalog>', 'indexsize') -- Size of the
full-text index
See BOL title FULLTEXTCATALOGPROPERTY for more info.
Thanks,
John
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:#mC3OeQsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>|||Hi James,
I think this will get you what you want ...
select FulltextCatalogProperty('CatalogName', 'ItemCount')
"James Brett" wrote:

> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>
>

number of rows in catalog

Hi
Is there a system query that will tell me the number of rows in a given
catalog?
Cheers
James> Is there a system query that will tell me the number of rows in a given
> catalog?
Here is an easy way:
EXEC sp_MSforeachtable 'sp_spaceused ''?'', ''true'''
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||This will return a good estimate of the number of rows in each table:
SELECT A.name, B.rows
FROM sysobjects A
JOIN sysindexes B ON A.ID = B.ID
WHERE A.type = 'U'
AND B.INDID < 2
ORDER BY A.Name
It is important to know that this is an estimate. The numbers are usually
pretty close. If you find that they are off you may have to issue several
DBCC statements (such as updateusage).
--
Keith
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%23mC3OeQsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>|||I was actually after the number of rows in the full text index catalog.
Any clues?
Cheers
James
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23hU7YjRsEHA.1248@.TK2MSFTNGP10.phx.gbl...
> > Is there a system query that will tell me the number of rows in a given
> > catalog?
> Here is an easy way:
> EXEC sp_MSforeachtable 'sp_spaceused ''?'', ''true'''
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
>
>|||to get an accurate number for each table you would
select count(*) from table
For an estimate you might
select name, rowcnt from sysindexes where id > 100 and indid in (0,1)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%23mC3OeQsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>|||catalogs don't contain words per se, but rather unique words. Use this
query to get an idea of the number of unique words
select FulltextCatalogProperty('CatalogName', 'UniqueKeyCount')
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:eqFnnxRsEHA.1988@.TK2MSFTNGP11.phx.gbl...
> I was actually after the number of rows in the full text index catalog.
> Any clues?
> Cheers
> James
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:%23hU7YjRsEHA.1248@.TK2MSFTNGP10.phx.gbl...
> > > Is there a system query that will tell me the number of rows in a
given
> > > catalog?
> >
> > Here is an easy way:
> > EXEC sp_MSforeachtable 'sp_spaceused ''?'', ''true'''
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > Associate Mentor
> > Solid Quality Learning
> > More than just Training
> > www.SolidQualityLearning.com
> >
> >
> >
> >
>|||James,
You can use the following metadata Full Text Search (FTS) queries to obtain
info from the FT Catalog:
select FulltextCatalogProperty('<FT_Catalog>', 'UniqueKeyCount') -- Number
of unique words
select FullTextCatalogProperty('<FT_Catalog>', 'itemcount') -- row count + 1
select FullTextCatalogProperty('<FT_Catalog>', 'indexsize') -- Size of the
full-text index
See BOL title FULLTEXTCATALOGPROPERTY for more info.
Thanks,
John
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:#mC3OeQsEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>|||Hi James,
I think this will get you what you want ...
select FulltextCatalogProperty('CatalogName', 'ItemCount')
"James Brett" wrote:
> Hi
> Is there a system query that will tell me the number of rows in a given
> catalog?
> Cheers
> James
>
>

Number of rows in a table without using count(*)

In a script I would like to have the approximate number
of rows of a table.
Is there an easy way to get the 'approximate' number
of rows in a table without using count(*).
ben brugmanHi,
Use the below query,
select object_name(Id) as table_name,rows from sysindexes where indid=0
Thanks
Hari
MCDBA
"ben brugman" <ben@.niethier.nl> wrote in message
news:#0zv5NkuDHA.1888@.TK2MSFTNGP10.phx.gbl...
> In a script I would like to have the approximate number
> of rows of a table.
> Is there an easy way to get the 'approximate' number
> of rows in a table without using count(*).
> ben brugman
>|||Hari
Why just no use
sp_spaceused 'table'
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OUBPZWkuDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Use the below query,
> select object_name(Id) as table_name,rows from sysindexes where indid=0
> Thanks
> Hari
> MCDBA
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:#0zv5NkuDHA.1888@.TK2MSFTNGP10.phx.gbl...
> > In a script I would like to have the approximate number
> > of rows of a table.
> >
> > Is there an easy way to get the 'approximate' number
> > of rows in a table without using count(*).
> >
> > ben brugman
> >
> >
>|||approx=not accurate if stat is not uptodate.
select rows
from sysindexes
where id=object_id('your table name')
and indid<2
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"ben brugman" <ben@.niethier.nl> wrote in message
news:%230zv5NkuDHA.1888@.TK2MSFTNGP10.phx.gbl...
> In a script I would like to have the approximate number
> of rows of a table.
> Is there an easy way to get the 'approximate' number
> of rows in a table without using count(*).
> ben brugman
>|||Thanks, Hari, Uri, Oj
this is exactly what I have been looking for.
(I did look at the sysindexes and sysobjects yesterday, but
missed the rows entry in the sysindexes table. Why, I do not know).
(Space_used uses the same numbers but it is more difficult to get
space_used results in a table).
Thanks again,
ben brugman
"ben brugman" <ben@.niethier.nl> wrote in message
news:#0zv5NkuDHA.1888@.TK2MSFTNGP10.phx.gbl...
> In a script I would like to have the approximate number
> of rows of a table.
> Is there an easy way to get the 'approximate' number
> of rows in a table without using count(*).
> ben brugman
>

Number of rows in a table

Hi,
Is there a quicker way to get the number of rows in a table other than the
select statement? I did see that there is a table object but there is no
detailed examples in the books online. Thanks for any help.
EllieP.S. I should have mentioned that my select statement selects a recordset of
the entire table and then gets the recordcount.
"Ellie" <nospam@.nospam.net> wrote in message
news:Op3cPtDcGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Is there a quicker way to get the number of rows in a table other than the
> select statement? I did see that there is a table object but there is no
> detailed examples in the books online. Thanks for any help.
> Ellie
>|||try this..
hope this helps..
select rowcnt from sysindexes where indid in(0,1) and id =
object_id('<table_name>')|||If you need to get an accurate count in SQL 2000, use SELECT COUNT(*). This
is more efficient that the recordset method, unless you need the recordset
for other reasons anyway.
The sysindexes query omnibuzz suggested will return an approximate count in
SQL 2000. The count is accurate in SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellie" <nospam@.nospam.net> wrote in message
news:O7AxfvDcGHA.4896@.TK2MSFTNGP03.phx.gbl...
> P.S. I should have mentioned that my select statement selects a recordset
> of the entire table and then gets the recordcount.
> "Ellie" <nospam@.nospam.net> wrote in message
> news:Op3cPtDcGHA.4032@.TK2MSFTNGP02.phx.gbl...
>|||Thanks Dan. You are right. Thanks for pointing it out.
But I have a doubt here. If auto_update_statistics is on for the database
can we be sure of the rowcount or we still cannot rely on it'
and How do you we make it reliable in SQL Server 2005. By giving the async
statistics update option'
--
"Dan Guzman" wrote:

> If you need to get an accurate count in SQL 2000, use SELECT COUNT(*). Th
is
> is more efficient that the recordset method, unless you need the recordset
> for other reasons anyway.
> The sysindexes query omnibuzz suggested will return an approximate count i
n
> SQL 2000. The count is accurate in SQL 2005.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ellie" <nospam@.nospam.net> wrote in message
> news:O7AxfvDcGHA.4896@.TK2MSFTNGP03.phx.gbl...
>
>|||You should run DBCC Updateusage before querying on sysindexes table
Madhivanan|||Updating of statistics doesn't have anything to do with having updated rowco
unt in the sysindexes
(or 2005 counterpart) tables/views. These are two separate things.
If auto update statistics would also be the one to update rowcnt, then it wo
uld have to be done
reading all rows, which would be disastrous for a clustered index on a table
with, say 10,000,000
rows.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:F4E4270C-B43B-4B20-81FF-944D88CC3885@.microsoft.com...
> Thanks Dan. You are right. Thanks for pointing it out.
> But I have a doubt here. If auto_update_statistics is on for the database
> can we be sure of the rowcount or we still cannot rely on it'
> and How do you we make it reliable in SQL Server 2005. By giving the async
> statistics update option'
> --
>
>
> "Dan Guzman" wrote:
>|||sp_spaceused @.updateusage=true will correct the row count. However, there
is no telling how long the count will remain good.
In SQL 2005, the engine does a better job of maintaining the sysindexes row
count as changes occur. Theoretically, @.updateusage is not needed to fix
the row count in SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:F4E4270C-B43B-4B20-81FF-944D88CC3885@.microsoft.com...
> Thanks Dan. You are right. Thanks for pointing it out.
> But I have a doubt here. If auto_update_statistics is on for the database
> can we be sure of the rowcount or we still cannot rely on it'
> and How do you we make it reliable in SQL Server 2005. By giving the async
> statistics update option'
> --
>
>
> "Dan Guzman" wrote:
>|||I think this explains to me why the Rows count that's displayed in
Enterprise Manager on the Table/Properties screen is sometimes
incorrect. I'm using SQL Server 8.
Are SQL Server 8 and SQL Server 2000 the same version? I'm running on
XP/Pro.
Ron|||Yes, version 8 = SQL 2000 and version 9 = SQL 2005.
Hope this helps.
Dan Guzman
SQL Server MVP
"RonL" <sal_paradise_93@.yahoo.com> wrote in message
news:1146933523.780782.253580@.j33g2000cwa.googlegroups.com...
>I think this explains to me why the Rows count that's displayed in
> Enterprise Manager on the Table/Properties screen is sometimes
> incorrect. I'm using SQL Server 8.
> Are SQL Server 8 and SQL Server 2000 the same version? I'm running on
> XP/Pro.
> Ron
>sql

number of rows in a select stmt

hey all,

I am writing a sproc:

select @.strCourseNameRegFor = sal.CourseName , @.strSectionNoRegFor = sal.SectionNo,
@.dteStartDateRegFor = sal.StartDate, @.dteEndDateRegFor = sal.EndDate,
@.dteStartTimeRegFor = sal.StartTime, @.dteEndTimeRegFor = sal.EndTime,
@.strDaysOfWeekRegFor = sal.DaysOfWeek
from lars.dbo.tblSalesCourse as sal, lars.dbo.tblCourseCatalog as cat
where sal.SchoolYr = @.intRegForYear and rtrim(sal.SchoolTerm) = rtrim(@.strRegForTerm) and upper(rtrim(sal.CourseName)) = upper(rtrim(@.strCourseNamePrev))
and cat.NewStuAllowed = 0 and sal.CourseName = cat.CourseName and sal.Cancelled <> 1 and cat.SchoolYr = sal.SchoolYr
and sal.MaxNoStudents > sal.CurrNoStudents

I want to check if the select returned an empty set or not. I cannot use @.@.rowcount because i am assigning the values to the local vars. I tried

if @.strCourseNameRegFor is null
begin
set @.err = 'No courses';
end

but for some reason even if there are any records in the set, the if condition is getting satisfied. Can anyone help?This means that sal.CourseName has a value of NULL when the WHERE clause is satisfied.

But I don't understand why you can't use @.@.ROWCOUNT when assigning values to local variable.

The following code produces a value of 1 for @.@.ROWCOUNT:

use pubs
declare @.str char(12)
select @.str = au_id from authors where au_lname = 'smith'
select @.str, @.@.rowcount|||Thanks for the reply. I tried it in Query Analyzer and it prints out the value of @.@.rowcount but when I do the same thing in a sproc, it prints out 0 for @.@.rowcount even though the select returns 1 record. Heres my query in the sproc:

select @.strCourseNameRegFor = sal.CourseName , @.strSectionNoRegFor = sal.SectionNo,
@.dteStartDateRegFor = sal.StartDate, @.dteEndDateRegFor = sal.EndDate,
@.dteStartTimeRegFor = sal.StartTime, @.dteEndTimeRegFor = sal.EndTime,
@.strDaysOfWeekRegFor = sal.DaysOfWeek
from lars.dbo.tblSalesCourse as sal, lars.dbo.tblCourseCatalog as cat
where sal.SchoolYr = @.intRegForYear and rtrim(sal.SchoolTerm) = rtrim(@.strRegForTerm) and upper(rtrim(sal.CourseName)) = upper(rtrim(@.strCourseNamePrev))
and cat.NewStuAllowed = 0 and sal.CourseName = cat.CourseName and sal.Cancelled <> 1 and cat.SchoolYr = sal.SchoolYr
and sal.AvailOnLine = 1
print @.@.rowcount;|||The following will produce a value of 9 for @.@.ROWCOUNT:

use pubs
declare @.str varchar(25)
select @.str = au_id from authors where au_lname like '%s%'
select @.str, @.@.rowcount