ALTER PROCEDURE dbo.spDelStudentByID
@id int,
@result int output
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
SET NOCOUNT ON
/*不返回计数当 SET NOCOUNT 为 ON 时,
不返回计数(表示受 Transact-SQL 语句影响的行数)。
当 SET NOCOUNT 为 OFF 时,返回计数。 如果存储过程中
包含的一些语句并不返回许多实际的数据,
则该设置由于大量减少了网络流量 */
begin
BEGIN TRANSACTION
DELETE FROM T_Student where studentid=@id
IF @@error=0
begin
COMMIT TRANSACTION
set @result = 1
end
ELSE
begin
ROLLBACK TRANSACTION
set @result = 0
end
end
//执行上面的存储过程
public static int RunProcedure(string storedProcName, int ID)
{
using (SqlConnection connection = new SqlConnection(sqlconnection))
{
int result;
connection.Open();
SqlCommand command = new SqlCommand(storedProcName, connection);
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@id", SqlDbType.Int);
p1.Direction = ParameterDirection.Input;
SqlParameter p2 = new SqlParameter("@result", SqlDbType.Int);
p2.Direction = ParameterDirection.Output;
p1.Value = ID;
command.Parameters.Add(p1);
command.Parameters.Add(p2);
command.ExecuteNonQuery();
result = (int)command.Parameters["@result"].Value;
connection.Close();
return result;
}
}