[SQL] Count results from stored procedure (without return)

It’s been a while since I haven’t used SQL. Now that I’m using it on a daily basis at work, I may write a little more about SQL than before. So here is an article the aim of which is to show how to find the number of results returned by a stored procedure. It’s in fact a problem which can be easily solved.

So let’s say we have a table containing animals names. Nothing fancy, just something like this:

name
----
Cat
Dog
Rabbit

That’s it for the table. Now we need a stored procedure, so let’s assume we have one : findNameLike which take letters as parameter and returns animals names beginning with these letters.

EXEC findNameLike @beginWith = N'C'

Cat.
Yes, the above call will return Cat.

So, if we want to know how much results were found, we will just write the following code:

DECLARE @numberOfMatch INT
EXEC findNameLike @beginWith = N'C'
SELECT @numberOfMatch = @@ROWCOUNT

The number of results is now available in variable numberOfMatch.

That’s it.
But maybe you also don’t want the stored procedure to return results when you call it. So we need to modify our code a little so that results will be stored in a temporary table and thus not returned, but rather stored in the table.

DECLARE @numberOfMatch INT
DECLARE @tmpTable TABLE (
    name VARCHAR(25)
)

INSERT INTO @tmpTable 
EXEC findNameLike @beginWith = N'C'
SELECT @numberOfMatch = @@ROWCOUNT

I think we’re done here. In a few lines, we’re able to count the results from a stored procedure as well as suppressing its return.