CODEWITHSUNDEEP
sqlsql-server
Manoj Savalia
Manoj Savalia

Reputation: 1402

How to know total record return stored procedure in sql?

I have a stored procedure called usp_getTotalOrder which looks like

Select * from Order where CompanyID = 1;

Now, I have a table which contains stored procedure names.

In my BLL I have a stored procedure name. I want to create function which contain one parameter StoredProcedureName and returns the count of total rows which is like 

Declare @str varchar(50) 
Set @str='GetOrders';  // Stored Procedure Name
Exec @str

But it does not return total row count that I want to get from the stored procedure with its name in the function.

Any IDEA??? Please help.....

Upvotes: 0

Views: 13400

Answers (3)

pistipanko
pistipanko

Reputation: 775

You can return the count of rows by output parameter or return value from your SP. Of course you have to add that output parameter to your SP and set it with COUNT(*) inside the SP.

DECLARE @o_count INT

EXEC GetOrder @o_count = @count OUT

Upvotes: 1

csm8118
csm8118

Reputation: 1213

Take a look at @@ROWCOUNT

http://technet.microsoft.com/en-us/library/ms187316.aspx

You might be able to use something like:

Declare @str varchar(50) 
Set @str='GetOrders';  // Stored Procedure Name
Exec @str
SELECT @@ROWCOUNT

Upvotes: 5

PHeiberg
PHeiberg

Reputation: 29831

Do you want to count the number of rows in the result set from the sproc? In that case you could do:

INSERT INTO #Orders
Exec @str

SELECT COUNT(*)
FROM #Orders

or you could use a Table variable instead of the temp table.

Upvotes: 1

Related Questions