To read the article online, visit http://www.4GuysFromRolla.com/webtech/sqlguru/q020100-1.shtml

The SQL Guru Answers your Questions...


Today's question comes from Khan R:

How will I get a stored procedure to return the number of records in 2 tables?

I have table-A and table-B. In one stored procedure I want to count the records in Table-A (with a where clause) and all the records in table-B (with a where clause). Add the 2 results together and output the sum as the result of the stored procedure.

(Stepping in for the SQL Guru for this question is Scott Mitchell, webmaster of 4GuysFromRolla.com)

Hello Khan.

You can count the number of records in a table using the following query:

SELECT COUNT(*) FROM TableName WHERE SomeWhereClause

To create a stored procedure that would sum the number of rows in two tables with a static WHERE clause (that is, you have a predefined WHERE clause for each of the two tables), you could use the following code:

CREATE PROCEDURE sp_Test
AS
	-- Create two integer values
	DECLARE @tableOneCount int, @tableTwoCount int

	-- Get the number of rows from the first table
	SELECT @tableOneCount = (SELECT COUNT(*) FROM Table1
	                         WHERE WhereClause)
	SELECT @tableTwoCount = (SELECT COUNT(*) FROM Table2
							 WHERE WhereClause)

    -- Return the sum of the two table sizes
	SELECT TotalCount = @tableOneCount + @tableTwoCount

If you wanted to be able to pass in a dynamic WHERE clause for each of the two tables, you would need to create two varchar datatypes which would hold each of the two SQL strings. Then, you'd use the EXEC statement to execute the dynamic SQL query. You can read more on how to do this from a previous SQL Guru question:

  • Using the IN Notation in Stored Procedures

    Happy Programming!

    Scott...


  • Article Information
    Article Title: SQL Guru: Returning the Total Number of Rows in Two Tables
    Article Author: Scott Mitchell
    Article URL: http://www.4GuysFromRolla.com/webtech/sqlguru/q020100-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers