Geek Answers Handbook

The number of rows in a stored procedure from another stored procedure

I have various stored procedures. I need a stored procedure to execute the stored procedure, and then I return only the number of rows (the number of rows returned by the called procedure), and I need to get it in C # code.

What is the best way to do this?

Situation: I have various stored procedures that are called from C # code. Now for another purpose, I need to know the number of rows returned by the procedure (I don't want the rows returned, I just need the number of rows). I cannot change the most stored procedure, since many of my C # codes use them, so everything can break if I change the procedures. Therefore, I am blocking a procedure that will take input and execute the procedure (dynamically) and return the number of rows, but not the result or rows.

I can take a stored procedure string and execute it as

EXEC ('SearchWallpaper @Keyword = ''' + @Key + '''')

I have an Id as a column in every possible returned row. I could store identifiers and count this, but I don't know what if this is a better idea.

My database is quite large. I could use executeenonquery () in C #, but it returns -1 when I execute any stored procedure with it. What a better way. Thanks.

+4
source share
2 answers

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

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

Since you are using SQL Server, I can come up with one solution that is not necessarily beautiful.

Create a temporary table (table variable if you have a newer version of SQL Server). Then do:

 exec(` declare @t table ( <columns go here> ); insert into @t exec(''<your exec here>''); select @rowcount ');

And now, when I said this, I would recommend sp_executesql . This happens 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's debugging a secret state that occurs when you call a stored procedure inside an insert.

+2
source

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

Exit for the same Parent Name ProcessName

ShareDrafts Job12 Job03 ShareDrafts Job13 Job58

(2 lines affected)

2

(1 row (s) affected)

0
source

More articles:


All Articles