saving value of COUNT function result

14 Jan 2006 2:10 PM

hazz

using the following example, how would I save the value returned by the
COUNT function so that I can use
the result in a subsequent loop. thank you. -greg

USE AdventureWorks;
GO
SELECT COUNT(DISTINCT Title)
FROM HumanResources.Employee;
GO

14 Jan 2006 2:12 PM

Tom Moreau

Try:

declare @count int

SELECT @count = COUNT(DISTINCT Title)
FROM HumanResources.Employee;

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"hazz" <h***@sonic.net> wrote in message
news:elg6qPRGGHA.216@TK2MSFTNGP15.phx.gbl...

using the following example, how would I save the value returned by the
COUNT function so that I can use
the result in a subsequent loop. thank you. -greg

USE AdventureWorks;
GO
SELECT COUNT(DISTINCT Title)
FROM HumanResources.Employee;
GO

14 Jan 2006 2:31 PM

hazz

thank you Tom. -greg

Show quote

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:OapDLSRGGHA.1628@TK2MSFTNGP12.phx.gbl...
> Try:
>
> declare @count int
>
> SELECT @count = COUNT(DISTINCT Title)
> FROM HumanResources.Employee;
>
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
>
> "hazz" <h***@sonic.net> wrote in message
> news:elg6qPRGGHA.216@TK2MSFTNGP15.phx.gbl...
> using the following example, how would I save the value returned by the
> COUNT function so that I can use
> the result in a subsequent loop. thank you. -greg
>
> USE AdventureWorks;
> GO
> SELECT COUNT(DISTINCT Title)
> FROM HumanResources.Employee;
> GO
>
>
>

14 Jan 2006 2:18 PM

ML

USE AdventureWorks;
GO

declare @count int
set @count = (SELECT COUNT(DISTINCT Title)
FROM HumanResources.Employee);
GO

--

ML

---
http://milambda.blogspot.com/

14 Jan 2006 2:31 PM

hazz

Thank you ML . -greg

Show quote

"ML" <M*@discussions.microsoft.com> wrote in message
news:7E5127B1-25A7-4452-9409-B09A6D45DC70@microsoft.com...
> USE AdventureWorks;
> GO
>
> declare @count int
> set @count = (SELECT COUNT(DISTINCT Title)
> FROM HumanResources.Employee);
> GO
>
> --
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

15 Jan 2006 8:32 PM

--CELKO--

>> how would I save the value returned by the COUNT function so that I

can use the result in a subsequent loop? <<

A better question: why are you writing a loop in a declarative language
like SQL? You should be writing a single statment that returns the
desired set of data, not mimicking a 3GL program.

Also, why is an "employee" modeled as an attribute of Human Resources?
Aren't they entities, not attributes? Of course if you had a better
name for the data element, it might make sense.

--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.

*** Sent via Developersdex http://www.developersdex.com ***

15 Jan 2006 11:49 PM

Erland Sommarskog

--CELKO-- (remove.jcelko***@earthlink.net) writes:

> A better question: why are you writing a loop in a declarative language
> like SQL? You should be writing a single statment that returns the
> desired set of data, not mimicking a 3GL program.

We all have to start somewhere.

> Also, why is an "employee" modeled as an attribute of Human Resources?
> Aren't they entities, not attributes? Of course if you had a better
> name for the data element, it might make sense.

Oh, cut the crap, Celko! That was an example from the AdventureWorks
database that comes with SQL 2005. If you want to answer questions
about SQL Server, first learn the product!

Had it been anyone else, who had made this error I would have politely
pointed out the mistake, but I am completely fed up with your attitude.
Don't you really have anything better to spend you days but harassing new
users of SQL Server?

Go and dig yourself under a rock!

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

17 Jan 2006 3:01 AM

--CELKO--

>> That was an example from the AdventureWorks database that comes with SQL 2005. <<

Yep, we have bad sample databases in many products. The Scott/Tiger
from Oracle is a proprietary nightmare with a bad tree structure. The
Presidents DB that comes with Centura (nee Gupta) had improper keys.
This one sucks, too.

>> Don't you really have anything better to spend you days but harassing new users of SQL Server? <<

Write another book? :)

I want people to learn how to do it right, not just tell them how use
some kludge to get over a problem. Also, did you notice that I only
asked questions about the decisions made? (like that last sentence!) I
am getting away from Zen and moving to Socratic methods this year.

16 Jan 2006 8:13 PM

Hugo Kornelis

On Sat, 14 Jan 2006 06:10:10 -0800, hazz wrote:

(snip)

>FROM HumanResources.Employee;

On Sun, 15 Jan 2006 12:32:39 -0800, --CELKO-- wrote:

(snip)

>Also, why is an "employee" modeled as an attribute of Human Resources?

Hi Joe,

Since when are attributes included in a FROM clause?

If you had bothered to actually read Greg's question and the SQL he
included, you'd have seen this.
If you had bothered to take a minute or two to check the SQL Server 2005
syntax (Books Online can even be brwosed on Internet!), you'd have known
that "xx.yy" in a FROM clause is a table name, prefixed by the schema
name.

Years ago, you were bitching about the lacking schema support in SQL
Server. Funny that once it's introduced, you don't even recognise it!

--
Hugo Kornelis, SQL Server MVP