CODEWITHSUNDEEP
sqlstored-procedures
GarbageGuy
GarbageGuy

Reputation: 513

Counting results of stored procedure

I have a stored procedure returning ID, Name, Descriptions and takes no input parameters. However, I am interested in how many results do I get.

I expected something like this work:

SELECT COUNT(*) FROM EXEC MyStoredProcedure

But I get the following error in SqlServer Managment Studio: Incorrect syntax near the keyword 'EXEC'. Could you show me a little code example how can I do that?

Upvotes: 18

Views: 46906

Answers (4)

Quassnoi
Quassnoi

Reputation: 425391

SELECT @@ROWCOUNT

Upvotes: 4

Charles Graham
Charles Graham

Reputation: 24835

You need to put the logic in the stored proc and return the count from the stored proc. You do this by using the @@ROWCOUNT variable immediately after your query. This ihow it would work in MS SQL Servet at least.

Stored Proc:

CREATE PROC MyPROC
AS
DECLARE @MyCount int

...

SELECT * FROM MyTable WHERE ...

SELECT @MyCount = @@ROWCOUNT

...

return @MyCOunt

Calling code:

DECLARE @MyCount int

EXEC @MyCount = EXEC MyProc

Upvotes: 4

Chris Simpson
Chris Simpson

Reputation: 7990

This won't work. May I suggest:

exec MyStoredProcedure
select @@rowcount

Alternatively you could return the count as an output parameter

Upvotes: 30

tehvan
tehvan

Reputation: 10369

Write a new stored procedure which does a count for you.

Upvotes: -4

Related Questions