Menu
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?
526
How many values can be returned from a stored procedure? Explanation: In MySQL, unlike the stored functions, the stored procedures cannot return values. They can be used to perform calculations or produce the result sets passed back to the clients. 4.
You can use the return statement inside a stored procedure to return an integer status code (and only of integer type). By convention a return value of zero is used for success. If no return is explicitly set, then the stored procedure returns zero. You should use the return value for status codes only.
This won't work. May I suggest:
exec MyStoredProcedure
select @@rowcount
Alternatively you could return the count as an output parameter
120
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
