使用命令时替换工作函数

exec MyStoredProcedure param1, param2, 

等... 但当它通过 C# 代码运行时, 它没有正确替换文本, 所以我最后会使用电子邮件, 上面仍然写着“ Comments ”, 而不是实际的评论。 我试图在 SQL Server 2008 中检查它, 但是它很好, 电子邮件通过正常。 需要检查什么吗?

这是我的C#代码:

public static void SetRequestStatusChange(int reqID, int newStatus, string assignTo)
{
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("spUpdateRequest", con);
    cmd.CommandType = CommandType.StoredProcedure;

    // Status
    cmd.Parameters.Add(new SqlParameter("@RequestNo", SqlDbType.Int, 4));
    cmd.Parameters["@RequestNo"].Value = 276;

    // Queue
    cmd.Parameters.Add(new SqlParameter("@userID", SqlDbType.NVarChar, 12));
    cmd.Parameters["@userID"].Value = "1091912";

    // State
    cmd.Parameters.Add(new SqlParameter("@RequestStatus", SqlDbType.Int, 4));
    cmd.Parameters["@RequestStatus"].Value = 2;

    // Buyer Emp ID
    cmd.Parameters.Add(new SqlParameter("@assignedTo", SqlDbType.NVarChar, 12));
    cmd.Parameters["@assignedTo"].Value = "1091912";

    try
    {
        con.Open();
        cmd.ExecuteNonQuery();
    }
    catch (SqlException err)
    {
        throw new ApplicationException("Data Error." + err.ToString());
    }
    finally
    {
        //close the connection 
        con.Close();
    }
}

以下是存储程序中实际进行替换的部分。 实际存储程序非常长, 并呼叫许多其他存储程序( 我没有写), 读起来会非常麻烦 。

select @html = replace(replace(replace(@html,  @@COMMENTS , case when @activityID <> 20 then   The following comments were noted when the Request  else    end +

   case @ActivityID when  2 then   was submitted:   +  case when requestorComments >    then requestorComments  else  None  end
   when 0 then    
   when  4 then   was edited:   + isnull(L.Comments,   None ) 
   when  15 then   was edited:   + isnull(LL.Comments,   None ) 
   when  20 then   
   when  21 then    material was returned:   + case when dbo.fnLastComment(lamSpecRecallComments) >    then replace(dbo.fnLastComment(lamSpecRecallComments), : , :   ) else  None  end
   when  -21 then    material was returned:   + case when dbo.fnLastComment(lamSpecRecallComments) >    then replace(dbo.fnLastComment(lamSpecRecallComments), : , :   ) else  None  end
   when  8 then   was approved:   + case when ccMgrCOmments >    then ccMgrCOmments  else  None  end
   when  9 then   was approved:   + case when lamAprCOmments >    then lamAprCOmments  else  None  end
   when 13 then   was stored:   + case when dbo.fnLastComment(lamSpecCOmments) >    then dbo.fnLastComment(lamSpecCOmments)  else  None  end
   when -8 then   was closed:   + case when ccMgrCOmments >    then ccMgrCOmments  else  None  end
   when -9 then   was closed:   + case when lamAprCOmments >    then lamAprCOmments  else  None  end
   else    end), 

    @@PLEASE ,
   case when @activityID =  8 or @activityID = 9 or @activityID = 20 or @activityID = 2  then  Please click the link below to view the Request and ensure it is worked expeditiously. 
   else    end), @@RECALL ,@list)  

   from (select * from strgRequests where requestNumber = @requestID) r
   left join (select * from  ActivityLog  where TransactionID = @transactionID) L on 1 = 1
   left join (select * from  ActivityLineItemLog where TransactionID = @transactionID) LL on 1 = 1

Comments, Recall, and {请来自一个表格。 这样电子邮件就可以定制 。