如何在sql中返回受影响的最后一个查询的记录数

In my stored procedure I have used 2 insert statement.When I am executing sp using executenonquery(),it return me number of row affected by 2 statement. Suppose number of row affected by Query 1 is 2 and number of row affected by Query 2 is 3.It returns me 5.I want last query's result.That is 3 only.How to do that?

在我的存储过程中,我使用了2个insert语句。当我使用executenonquery()执行sp时,它返回受2个语句影响的行数。假设查询1影响的行数为2,查询2影响的行数为3.它返回5.我想要最后查询的结果。这只是3。如何做?

1 个解决方案

#1

You have 2 options here depending on your needs.

根据您的需要,您有2种选择。

Firstly you could toggle the NOCOUNT setting between your insert statements so the count for the inserts you're not interested in are ignored:

首先,您可以在插入语句之间切换NOCOUNT设置,以便忽略您不感兴趣的插入的计数:

ALTER PROCEDURE [dbo].[spFoo]

AS
BEGIN

set nocount on
    insert into test values (1, 'a')
    insert into test values (2, 'b')
set nocount off 
    insert into test values (3, 'c')


END

This will return 1 when called with ExecuteNonQuery.

使用ExecuteNonQuery调用时,它将返回1。

Alternatively you could grab the number of rows affected by the last insert using @@ROWCOUNT. You can select that back and use ExecuteScalar to get the result in C#:

或者,您可以使用@@ ROWCOUNT获取受上一次插入影响的行数。您可以选择返回并使用ExecuteScalar在C#中获取结果:

ALTER PROCEDURE [dbo].[spFoo]

AS
BEGIN

    insert into test values (1, 'a')
    insert into test values (2, 'b')
    insert into test values (3, 'c')

    select @@ROWCOUNT
END

You'll need to cast the result in this case as the return value of ExecuteScalar is object:

在这种情况下,您需要转换结果,因为ExecuteScalar的返回值是object:

int affected = (int)command.ExecuteScalar();

#1