一个存储过程在另一个存储过程中的行数 [英] Row count of a stored procedure from another stored procedure

问题描述

我有各种存储过程.我需要一个存储过程来执行一个存储过程，然后只返回行数(被调用过程返回的行数)，然后需要用c#代码接收它.

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?

情况: 我有各种从C#代码调用的存储过程.现在出于另一个目的，我需要知道一个过程返回的行数(我不希望返回的行，我只需要行数).我无法更改存储过程本身，因为许多C#代码正在使用它们，因此如果我更改存储过程，事情可能会中断.这就是为什么我锁定一个过程的原因，该过程将接受输入并(动态)执行一个过程，并返回行数，但不返回结果或行数.

Situation: I have various stored procedures which is called from c# code. Now for another purpose i need to know the number of rows returned by a procedure (I don't want the returned rows, I just the need the number of rows). I cannot change the stored procedure itself as many of my c# code is using them, so things may break if I change the procedures. That's why I am locking for a procedure which will take input and execute a procedure (dynamically) and return the number of rows but not the result or rows.

我可以使用存储过程字符串 并像执行

I could take stored procedure string and execute it like

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

在每个可能返回的行中，我都有ID作为列.我可以存储ID并对其进行计数，但是我不知道这是否是最好的主意.

I have Id as a column in each possible returned row. I could store the ids and count it but I don't know what if that's the best idea.

我的数据库很大.我可以在c#中使用executenonquery()，但是当我用它执行任何存储过程时，它都返回-1.最好的方法是什么.谢谢.

My database is pretty big. I could use executenonquery() in c# but it's returning -1 when I execute any stored procedure with it. What's the best way. Thank you.

推荐答案

假定您正在使用SQL Server(可以从代码段中获取)，也许类似的方法对您有用:

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')

由于您正在运行SQL Server，因此我可以想到一种不一定很漂亮的解决方案.

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

创建一个临时表(如果您使用的是SQL Server的最新版本，则为表变量).然后执行:

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
');

现在我已经说过了，我建议使用sp_executesql.这是这样的:

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.

这篇关于一个存储过程在另一个存储过程中的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！