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: