5

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?

2 Answers2

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    +1 For `@@rowcount`... but he seems to indicate that he wants to somehow suppress the result set returned by the called stored procedure. – Michael Fredrickson Feb 06 '13 at 17:26
  • but it's returning me the rows also. and on the messeges i see 385 rows affected then again 1 row affected no server management studio –  Feb 06 '13 at 17:27
  • is there a way to have a single column from the result returned by the stored procedure.. like select id from exec 'stored procedure' –  Feb 06 '13 at 18:09
  • 1
    @KDB . . . There is not a way that I can readily think of. But because they are *your* stored procedure, why not have an optional output parameter that has the row count? You wouldn't have to change existing code and you can get what you want. – Gordon Linoff Feb 06 '13 at 18:43
  • i'll get into that. can you tell me how i'll be able read that out with c#. thanks for your reply. another thing is, when I execute my stored procedure with sql server management studio, there's a message there saying 555 rows are affected. can i get that message with c#? –  Feb 07 '13 at 14:45
  • @KDB . . . This answer might help you http://stackoverflow.com/questions/6210027/c-sharp-calling-sql-server-stored-procedure-with-return-value. – Gordon Linoff Feb 07 '13 at 15:56
0

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)

Himansz
  • 193
  • 2
  • 14