Row Count Of A Stored Procedure From Another Stored Procedure

I have various stored procedures. I need a stored procedure to execute a stored procedure and then return only the row count (number of returned rows by the called procedure) and I

Solution 1:

Assuming you are using SQL Server (which is possible from the code snippets), perhaps something like this would work for you:

exec('exec <your stored procedure goes here>; select @@RowCount')

Since you are running SQL Server, I can think of one solution that is not necessarily pretty.

Create a temporary table (table variable if you have a more recent version of SQL Server). Then execute:

exec(`
declare @t table (
   <columns go here>
);

insert into @t
    exec(''<your exec here>'');

select @rowcount
');

And now that I've said that, I would recommend sp_executesql. This goes something like this:

declare @sql nvarchar(max) = N'exec '+@YOURQUERY + '; set @RowCount = @@RowCount';

exec sp_executesql @sql, N'@RowCount int output', @RowCount = RowCount output;

I spent most of yesterday debugging an arcane condition that arises when you call a stored procedure inside an insert.

Solution 2:

You can try this in your child stored procedure :

CREATE PROC PawanXX
(
 @aINT
,@bINT OUTPUT
)
ASBEGINSELECT TOP 2*FROM X

SET@b= @@ROWCOUNTRETURN@bEND
GO

The main stored procedure where we call all other sps

DECLARE@RCintDECLARE@aintDECLARE@bintEXECUTE@RC= [dbo].[PawanXX] 
   @a
  ,@b OUTPUT

SELECT@RC

The output for the same ProcessName Parent Child

---

ShareDrafts Job12 Job03 ShareDrafts Job13 Job58

(2 row(s) affected)

---

2

(1 row(s) affected)