CODEWITHSUNDEEP
c#sqlstored-proceduresrowcountexecutenonquery
user1711245
user1711245

Reputation:

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 need to receive it in c# code.

What's the best way to do this?

Upvotes: 5

Views: 3634

Answers (2)

Himansz
Himansz

Reputation: 193

You can try this in your child stored procedure :

CREATE PROC PawanXX
(
 @a INT
,@b INT OUTPUT
)
AS
BEGIN

SELECT TOP 2 * FROM X

SET @b = @@ROWCOUNT

RETURN @b

END
GO

The main stored procedure where we call all other sps

 DECLARE @RC int
 DECLARE @a int
 DECLARE @b int

EXECUTE @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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

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.

Upvotes: 2

Related Questions