USE[TestDB]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_ProcedureWithTransaction_Demo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
DROP PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo] 
GO
-- =============================================
-- Author:        <>
-- Create date: <>
-- Description:    <存储过程中使用事务>
-- =============================================
CREATE PROCEDURE [dbo].[USP_ProcedureWithTransaction_Demo]
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
UPDATE dbo.test SET num = 1 WHERE TypeID = 1;
UPDATE dbo.test SET num = 4 WHERE TypeID = 2; --此语句将出错，Phone为Int类型
            UPDATE dbo.test SET num = 'c' WHERE TypeID = 3;
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RETURN -1;
END CATCH    
RETURN 1;
END

/// <summary>
/// 执行存储过程，无参数，有返回值。存储过程执行成功时返回1，否则返回0。
/// </summary>
/// <param name="spName">存储过程名称</param>
/// <returns>0：出错；1：成功</returns>
        public int SpWithReturnValueExecuteNonQuery(string spName)
        {
int result = 0;
            SqlConnection cnn = GetConnection();
            SqlTransaction trans = null;
            SqlCommand cmd = new SqlCommand(spName, cnn);

            cmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

            cmd.CommandTimeout = _commandTimeout;
            cmd.CommandType = CommandType.StoredProcedure;

try
            {
                cmd.ExecuteNonQuery();
                result = (int)cmd.Parameters["@ReturnValue"].Value;
            }
catch (SqlException)
            {
return result;
            }
finally
            {
                cnn.Close();
            }
return result;
        }