ExecuteNonQuery()执行存储过程的返回值问题?

[hxh016]

问题是这样的：

MSND上说; ExecuteNonQuery 在执行UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。
可是我用的时候，一直返回-1（-1代表执行失败！）
我从数据库里看到，执行成功。

create table StudentResume --创建一个表
(
StdID int,
StudentID nvarchar(50),
InitiationTime datetime,
TerminationTime datetime,
LocationIN nvarchar(50),
WorkContent nvarchar(MAX),
Position nvarchar(50),
Authenticator nvarchar(50),
Notes nvarchar(MAX)
)

//创建一个存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE [dbo].[prInsertStudentResume]
-- Add the parameters for the stored procedure here
@StdID int,
@StudentID nvarchar(50),
@InitiationTime datetime,
@TerminationTime datetime,
@LocationIN nvarchar(50),
@WorkContent nvarchar(MAX),
@Position nvarchar(50),
@Authenticator nvarchar(50),
@Notes nvarchar(MAX)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
if(@InitiationTime='')
begin
set @InitiationTime=null
end
if(@TerminationTime='')
begin
set @TerminationTime=null
end
if(@LocationIN='')
begin
set @LocationIN=null
end
if(@WorkContent='')
begin
set @WorkContent=null
end
if(@Position ='')
begin
set @Position=null
end
if(@Authenticator='')
begin
set @Authenticator=null
end
if(@Notes='')
set @Notes=null
/*--
-- 定义一个局部变量，保存@@rowCount的值
*/
declare @rows int
select @rows = @@rowCount

--然后执行插入
insert StudentResume(StdID,StudentID,InitiationTime,TerminationTime,LocationIN,WorkContent,Position,Authenticator,Notes)
values(@StdID,@StudentID,@InitiationTime,@TerminationTime,@LocationIN,@WorkContent,@Position,@Authenticator,@Notes)

select @rows = @@rowCount

declare @ErrorCode int
select @ErrorCode=@@Error
if @ErrorCode<>0
begin
RaisError('Error occurred while inserting into StudentResume!',16,1)
Return @ErrorCode
end
--判断，如果@rows为0，则
if @rows = 0
Print 'No rows were inserted!'

--如果@rows为1,则显示1
if @rows = 1
Print '1'
--返回@rows的值，代表@@rowCount 修改的行数
Return @rows
END

然后执行这段存储过程（这是petshop4上的SQLHelper上的一段程序）
运行代码函数 //相关参数都以准确的得到
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{

SqlCommand cmd = new SqlCommand();

using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery(); //这里始终是返回-1，在SQL2005的查询分析器中执行存储过程可以返回的正确的结果是1（正确）
cmd.Parameters.Clear();
return val;
}
}

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{

if (conn.State != ConnectionState.Open)
conn.Open();

cmd.Connection = conn;
cmd.CommandText = cmdText;

if (trans != null)
cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}

[tesling]

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

============================================
上面是msdn里的解释，很清楚吧

[tesling]

ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.NETDEVFX.v20.en/cpref4/html/M_System_Data_SqlClient_SqlCommand_ExecuteNonQuery.htm

[babyrockxray]

存储过程里面既然return @row了
就调用ExcuteScalar()来获得返回值

[最后一只恐龙]

你的ExecuteNonQuery返回的是执行存储过程的返回值，而不是返回的你的Return @rows 。
只有语句ExecuteNonQuery的返回值才有效。

[Arieslns]

貌似除了Update,insert,delete语句之外都返回-1吧

[zq200487731]

For all other types of statements, the return value is -1.