CODEWITHSUNDEEP
sql-serverselectcount
rjbogz
rjbogz

Reputation: 870

getting the count of a subquery

I am looking to get the count of a subquery, or at least a way around it.

I have a stored procedure that I create a temp table later on in it, but I need the number of rows that gets returned in that temp table.

The way that I get the temp table is from a select with a few joins. Is there any way to get the number of rows that is returned in that select statement?

I have something similar to:

SELECT Book.Title,
       Book.Year,
       BookGenre.Genre
  FROM Book
  JOIN BookGenre ON Book.BookNum = BookGenre.BookNum
 WHERE Book.Title IS NOT NULL

and before then, I need to get the count of the rows that will be returned from that query. So, essentially:

COUNT(SELECT Book.Title,
             Book.Year,
             BookGenre.Genre
        FROM Book
        JOIN BookGenre ON Book.BookNum = BookGenre.BookNum
       WHERE Book.Title IS NOT NULL)

Upvotes: 0

Views: 105

Answers (1)

HABO
HABO

Reputation: 15816

@@RowCount immediately after the SELECT will give you the number of rows returned.

You can either get the count and then repeat the query, or execute the query and get the count. The query results can be directed to a temporary table or table variable if you need to use the count before further processing of the data.

In some cases it may be helpful to run the query, but only save the id's needed to retrieve the full data later. Your example doesn't seem to be one of those cases.

For completeness:

Denormalizing is another choice. For example, you could use triggers to maintain a count of the number of books in each genre within the BookGenre table.

Maintaining a cache of results is another possibility if the queries are expensive, repeated and you can tolerate a degree of stale data. For example, a Top Ten list on a website home page doesn't need to be precisely correct. Running the query every 5 minutes might be adequate to preserve the illusion of currency.

Be careful to avoid Premature Optimization. You ought to have a specific and measurable performance problem before you start looking into optimization.

Upvotes: 3

Related Questions