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...
