ADO.NET+存储过程+事务的一个奇怪问题，求探讨

一段程序代码如下：

public int SetUserRoleAndRange(int uid, string roleIDList, string rangeIDList)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@UID", SqlDbType.Int,4),
                    new SqlParameter("@RoleIDList", SqlDbType.NVarChar),
                    new SqlParameter("@RangeIDList", SqlDbType.NVarChar),
                    new SqlParameter("@ReturnValue",SqlDbType.Int,4)};
            parameters[0].Value = uid;
            parameters[1].Value = roleIDList;
            parameters[2].Value = rangeIDList;
            parameters[3].Direction = ParameterDirection.ReturnValue;
            SqlHelper.ExecuteNonQuery("EF_Account_SetUserRoleAndRange", parameters);
            return (int)parameters[3].Value;
        }

其中，uid=1693，RoleIDList='',RangeDIList=''.调试结果如下：

注意，这里我们将SQLHelper类进行了一定的改造，在PrepareCommand方法中，我们将参数中的NULL和“”全部转化为了DBNull.Value，代码如下：

foreach (SqlParameter parm in cmdParms)
                {
                    if ((parm.Value == null) || (string.IsNullOrEmpty(parm.Value.ToString())))
                    {
                        parm.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parm);
                }

下面是我们的存储过程代码：

USE [EasyFast.OA]
GO
/****** Object:  StoredProcedure [dbo].[EF_Account_SetUserRoleAndRange]    Script Date: 2012/4/19 12:35:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[EF_Account_SetUserRoleAndRange]
@UID INT,
@RoleIDList NVARCHAR(MAX),
@RangeIDList 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
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @Index NVARCHAR(MAX)
    SET @SQL = 'DELETE FROM EF_UserRoles WHERE UID = '+CONVERT(NVARCHAR,@UID)+';';
    SET @SQL = @SQL + 'DELETE FROM EF_UserRange WHERE UID = '+CONVERT(NVARCHAR,@UID)+';';

    SET @Index = 1;
    WHILE @Index <= dbo.Get_StrArrayLength(@RoleIDList,',')
    BEGIN
        SET @SQL = @SQL + 'INSERT INTO EF_UserRoles (UID,RID) VALUES ('+CONVERT(NVARCHAR,@UID)+','+dbo.Get_StrArrayStrOfIndex(@RoleIDList,',',@Index)+');';
        SET @Index = @Index + 1;
    END

    SET @Index = 1;
    WHILE @Index <= dbo.Get_StrArrayLength(@RangeIDList,',')
    BEGIN
        SET @SQL = @SQL + 'INSERT INTO EF_UserRange (UID,OfficeID) VALUES ('+CONVERT(NVARCHAR,@UID)+','+dbo.Get_StrArrayStrOfIndex(@RangeIDList,',',@Index)+');';
        SET @Index = @Index + 1;
    END

    BEGIN TRY
        BEGIN TRAN
            EXECUTE sp_executesql @SQL;
        COMMIT TRAN
        return 1;
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        RETURN 0
    END CATCH
END

数据库中原有数据如下：

执行如下存储过程，系统提示成功：

按理说，当返回1时，至少执行过两次DELETE语句了，但是我再次查询数据库，发现原有的数据还在。且自增ID都没变化，这说明delete语句没有执行（是否是事务回滚了，如果是回滚，应该返回0才对啊）。纠结了，想不明白哪里出错了，请大家一起讨论下。