Can I do this? - 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)

Can I do this?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-08-31 : 20:56:46

Im getting crazy errors on this :) .. Can't this be done? If not what is the best approach?

Thanks

MIke123

CREATE PROCEDURE select_count_maintenance

AS SET NOCOUNT ON

SELECT count(userID) AS newUsers FROM tblUsers WHERE active = '2',
SELECT count(userID) AS forDeletion FROM tblUsers WHERE active = '3' ,
SELECT count(userID) AS forRevalidation FROM tblUsers WHERE active = '4' ,
SELECT count(userID) AS unReverifiedEmail FROM tblUsers WHERE active = '8',
SELECT count(userID) AS unVerifiedEmailFROM FROM tblUsers WHERE active = '9'

GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-31 : 21:04:25

Sure you can do it. You'll get 5 sepearate result sets.

A single result set with 5 columns would be:

SELECT Sum(CASE active WHEN '2' THEN 1 ELSE 0 END) AS newUsers,
Sum(CASE active WHEN '3' THEN 1 ELSE 0 END) AS forDeletion,
Sum(CASE active WHEN '4' THEN 1 ELSE 0 END) AS forRevalidation,
Sum(CASE active WHEN '8' THEN 1 ELSE 0 END) AS unReverifiedEmail,
Sum(CASE active WHEN '9' THEN 1 ELSE 0 END) AS unVerifiedEmail
FROM tblUsers

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-09-01 : 15:39:11

That works perfectly and is already way faster, is it possible to add another select statement for another table and still return it as a single result set?

For instance, adding this in.

SELECT count(userID) AS newThumbs FROM tblThumbs where status = '0'

Thanks

MIke123

Edited by - mike123 on 09/01/2002 15:40:17

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-09-01 : 17:06:37


SELECT Sum(CASE active WHEN '2' THEN 1 ELSE 0 END) AS newUsers, 
Sum(CASE active WHEN '3' THEN 1 ELSE 0 END) AS forDeletion, 
Sum(CASE active WHEN '4' THEN 1 ELSE 0 END) AS forRevalidation, 
Sum(CASE active WHEN '8' THEN 1 ELSE 0 END) AS unReverifiedEmail, 
Sum(CASE active WHEN '9' THEN 1 ELSE 0 END) AS unVerifiedEmail,
(SELECT count(userID) FROM tblThumbs where status = '0') AS newThumbs
FROM tblUsers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources