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.