When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
Print this page.
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 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!


    Read Other SQL Guru Questions

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article