一段程序代码如下：

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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        
     
      -- Create date: 
      
       -- Description:    
       
        -- =============================================ALTER PROCEDURE [dbo].[EF_Account_SetUserRoleAndRange]@UID INT,@RoleIDList NVARCHAR(MAX),@RangeIDList NVARCHAR(MAX)ASBEGIN    -- 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 CATCHEND
       
      
     

 

数据库中原有数据如下：

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

 

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