CREATE PROCEDURE s_AddSelectResult --增加选课纪录
(
@YearTermID char(5), --学年学期
@CourID char(6), --课程号
@StudID char(11), --学号
@CourOrder smallint, --课序号
@Reupflag smallint, --重修标志
@rst nvarchar(30) out --返回结果
)AS
DECLARE
@row char(4)--首先判断是否存在相同的纪录
IF (not exists (select * from s_selectresult where yeartermid = @YearTermID and courid = @CourID and studid = @StudID and CourOrder = @CourOrder))
BEGIN
--插入纪录
Insert into s_SelectResult(YearTermID, CourID, StudID, CourOrder, ReupFlag, BeSelected, CancelFlag,
SelectFlag, MateFlag, LastDate, Operator)
values(@YearTermID,@CourID,@StudID,@CourOrder,@ReupFlag,1,0,0,0,getdate(),'yixian') --判断是否插入成功
IF ( @@rowcount = 1)
BEGIN
SET @rst = '增加成功'
END
END
ELSE
BEGIN
SET @rst = '失败,该生已选过该课程'
END
print @rst
select @rst
GO结果如下 :增加成功
------------------------------
增加成功
@RETURN_VALUE = 0事实上,我是想得到@rst的值,但使用return @rst会报错,说将'失败,该生已选过该课程'转换为int类型时出错.
为什么要转换成int类型呢?
而且不论如何,都会有@RETURN_VALUE = 0返回,这又是怎么回事?
最后,select @rst的结果如何在C#,winform中接收?
(
@YearTermID char(5), --学年学期
@CourID char(6), --课程号
@StudID char(11), --学号
@CourOrder smallint, --课序号
@Reupflag smallint, --重修标志
@rst nvarchar(30) out --返回结果
)AS
DECLARE
@row char(4)--首先判断是否存在相同的纪录
IF (not exists (select * from s_selectresult where yeartermid = @YearTermID and courid = @CourID and studid = @StudID and CourOrder = @CourOrder))
BEGIN
--插入纪录
Insert into s_SelectResult(YearTermID, CourID, StudID, CourOrder, ReupFlag, BeSelected, CancelFlag,
SelectFlag, MateFlag, LastDate, Operator)
values(@YearTermID,@CourID,@StudID,@CourOrder,@ReupFlag,1,0,0,0,getdate(),'yixian') --判断是否插入成功
IF ( @@rowcount = 1)
BEGIN
SET @rst = '增加成功'
END
END
ELSE
BEGIN
SET @rst = '失败,该生已选过该课程'
END
GOMySqlParameter[] parameters = {
//参数略...
};
parameters[5].Direction = ParameterDirection.Output;
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, System.Data.CommandType.StoredProcedure, "amendName", parameters);
string result = parameters[5].Value;//获得返回值
//参数略...
};
parameters[5].Direction = ParameterDirection.Output;
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, System.Data.CommandType.StoredProcedure, "存储过程名", parameters);
string result = parameters[5].Value;//获得返回值
C#接收存储过程返回值,可参考以下代码
public static int User_Add(User us)
{
int iRet;
SqlConnection conn = new SqlConnection(Conn_Str);
SqlCommand cmd = new SqlCommand("User_Add", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UName", us.UName);
cmd.Parameters.AddWithValue("@UPass", us.UPass);
cmd.Parameters.AddWithValue("@PassQuestion", us.PassQuestion);
cmd.Parameters.AddWithValue("@PassKey", us.PassKey);
cmd.Parameters.AddWithValue("@Email", us.Email);
cmd.Parameters.AddWithValue("@RName", us.RName);
cmd.Parameters.AddWithValue("@Area", us.Area);
cmd.Parameters.AddWithValue("@Address", us.Address);
cmd.Parameters.AddWithValue("@ZipCodes", us.ZipCodes);
cmd.Parameters.AddWithValue("@Phone", us.Phone);
cmd.Parameters.AddWithValue("@QQ", us.QQ);
cmd.Parameters.Add("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;
try
{
conn.Open();
cmd.ExecuteNonQuery();
iRet = (int)cmd.Parameters["@RETURN_VALUE"].Value;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return iRet;
} C#接收存储过程输出参数:
public static decimal Cart_UserAmount(int UID)
{
decimal iRet;
SqlConnection conn = new SqlConnection(Conn_Str);
SqlCommand cmd = new SqlCommand("Cart_UserAmount", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UID", UID);
cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Direction=ParameterDirection.Output;
try
{
conn.Open();
cmd.ExecuteNonQuery();
iRet = (decimal)cmd.Parameters["@Amount"].Value;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
return iRet;
}
stringbuilder str = new stringbuilder("insert into table1 values(test,name);select @@identity")
string result = string.empty();
//得到新插入的ID
result=(string)cmd.excutescalr(str.tostring());
如果是获取多个返回值,可以参考4楼,已经很详细了。