dbCommand ，必须声明变量错误

Question

这是我的代码。我也添加了 db 参数，但它仍然显示错误（在执行时）。必须声明标量变量

       DbCommand command;
        StringBuilder query = new StringBuilder(
                                                        @"SELECT         isnull(UpsellService_OID,'') UpsellService_OID," + Environment.NewLine +
                                                        "       isnull(ServiceName,'') ServiceName," + Environment.NewLine +
                                                        "       isnull(ServiceDescription,'') ServiceDescription," + Environment.NewLine +
                                                        "       isnull(Create_By,'') Create_By," + Environment.NewLine +
                                                        "       isnull(Create_Date,'') Create_Date," + Environment.NewLine +
                                                        "       isnull(Modify_By,'') Modify_By," + Environment.NewLine +
                                                        "       isnull(Modify_Date,'') Modify_Date," + Environment.NewLine +
                                                        "       isnull(Active_f,'') Active_f" + Environment.NewLine +
                                                        "FROM   TRGPAYROLL.ZONG.UPSELLSERVICES   " + Environment.NewLine +
                                                        "WHERE  1 = 1");
        if (!string.IsNullOrEmpty(idObject.ServiceName))
        {
            query.Append(" AND ServiceName like '%'  @ServiceName  '%'");
        }
        command = db.GetSqlStringCommand(query.ToString());
        if (!string.IsNullOrEmpty(idObject.ServiceName))
        {
            db.AddInParameter(command, "ServiceName", DbType.String, idObject.ServiceName);
        }
        return command;

Answer 1

我会以这种方式重写代码的最后一部分

    if (!string.IsNullOrEmpty(idObject.ServiceName))
    {
        query.Append(" AND ServiceName like @ServiceName");
    }
    command = db.GetSqlStringCommand(query.ToString());
    if (!string.IsNullOrEmpty(idObject.ServiceName))
    {
        db.AddInParameter(command, "@ServiceName", DbType.String, "%" + idObject.ServiceName + "%");
    }

通配符直接添加到参数的值中，而参数的占位符应没有任何字符串串联。但是，缺少许多细节以确保此答案的正确性。特别是我只能假设方法的内部工作原理GetSqlStringCommand和AddInParameter

Answer 2

@ServiceName变量不会在 SQL 语句中声明。在它的开头附加类似的东西

DECLARE @ServiceName AS nchar(32)
SET @ServiceName = ....