COUNT(*) from multiple tables

I want the total number of records from 2 child tables that are related to a parent table.
This doesn't work:

CREATE PROCEDURE [MyProcedure]
(
      @MyPrimaryKey,
      @Count OUTPUT
)
AS

      SET @Count =
                   SELECT COUNT(*) FROM Table1 WHERE ForeignKey  = @MyPrimaryKey AND Active = 1
        +
         SELECT COUNT(*) FROM Table2 WHERE ForeignKey  = @MyPrimaryKey AND Active = 1

      RETURN @Count
GO

Anyone?
LVL 3
natejacobsAsked:
Who is Participating?
 
rafranciscoCommented:
Try this:

DECLARE @RowCount INT

EXEC MyProcedure 135, @RowCount OUTPUT

PRINT @RowCount
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Maybe...

CREATE PROCEDURE [MyProcedure]
(
     @MyPrimaryKey,
     @Count OUTPUT
)
AS

declare @temp1 int
declare @temp2 int

SET @temp1 = SELECT COUNT(*) FROM Table1 WHERE ForeignKey  = @MyPrimaryKey AND Active = 1
SET @temp2 = SELECT COUNT(*) FROM Table2 WHERE ForeignKey  = @MyPrimaryKey AND Active = 1

@Count = @temp1 + @temp2
RETURN @Count
GO
0
 
rafranciscoCommented:
or this one:

CREATE PROCEDURE [MyProcedure]
(
     @MyPrimaryKey VARCHAR(10),
     @Count INT OUTPUT
)
AS

     SET @Count = (SELECT COUNT(*) FROM Table1 WHERE ForeignKey  = @MyPrimaryKey AND Active = 1 )
       +
        (SELECT COUNT(*) FROM Table2 WHERE ForeignKey  = @MyPrimaryKey AND Active = 1 )

     RETURN @Count
GO
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
natejacobsAuthor Commented:
Syntax works, it'll take a few minutes to get it into my app.
Related question:  How would you test this sp in Query Analyzer?

typing
MyProcedure 135

doesn't work

0
 
cyberdevil67Commented:
Hi natejacobs,

 This is off the top of my head, but you need to do a subselect

 select count(*) as Total1 from table1
 where (select Count(*) as Total2 from Table 2 where ForeignKey = @MyPrimaryKey)


Cheers!
0
 
natejacobsAuthor Commented:
Thanks for both answers, rafrancisco
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.