StringBuilder sbSql = new StringBuilder();
sbSql.Append(@"EXEC('select ID,PumpingName,ProLocat,ConsUnit,CreatedYear,DesignUnits,DrainArea,IrriqWater,ConArea,Enggrade,IrrigaStrutype,InfrastruType,ConstruArea,IrrigaDesign,IrrigaStatus,DrainDesign,DrainStatus,DesignFlow,StatusFlow,StatDesign,StatStatus,SituaDesign,SituaStatus,HeadDesign,HeadStatus,LiftsDesign,LiftsStatus,TotalDesign,TotalSitua,PaddyDesign,DrainSitua,DryDesign,GlebeStatus,TotalIrriDes,TotalIrriSta,PaddyDes,IrigaSta,IrriDesign,IrriStat, (select Max(cnt) from
(
select count(NULLIF(PumpModel,"")) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(NULLIF(PumpManu,"")) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(NULLIF(PumpUnits,"")) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(NULLIF(PumpTime,"")) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(NULLIF(PumpPower,"")) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
) t) PumpUnits,
(select Max(cnt) from
(
select count(EquipType) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(MachManuf) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(MachUnits) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(MachTime) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
union all
select count(PowerEquip) as cnt from FMDS_Water_IrrigaDrainageStat'+ @Condition +'group by Id
) t) MachUnits,EarthCumula,InitialWork,StoneWork,StoneInit,ConcrCumula,IniConSqu,Otheraccumu,Initial,SccumulaTotal,RaisedTotal,Sccumula,Sinitial,Raised,Initialself,Affiliation,Staff,Annualcost,CostSources,OperatStatus,AnnopeTime,AveragePower,IrDrID,IrDrName,Stake,DrainageID,DrainageName,IrrigationID,IrrigationName,ExistingProblems,PicCode,Remarks,Locations,ThreeCode,FMDS_Water_IrrigaDrainageStat.Longitude,FMDS_Water_IrrigaDrainageStat.Latitude,FMDS_Water_IrrigaDrainageStat.OrganiseUnitID,
FMDS_Water_IrrigaDrainageStat.OrganiseUnitName,FMDS_Water_IrrigaDrainageStat.CreatedBy,FMDS_Water_IrrigaDrainageStat.CreatedDate,FMDS_Water_IrrigaDrainageStat.ModifiedBy,FMDS_Water_IrrigaDrainageStat.ModifiedDate
from [FMDS_Water_IrrigaDrainageStat]
left join SDMS_OrganiseUnit on SDMS_OrganiseUnit.OrganiseUnitID=FMDS_Water_IrrigaDrainageStat.OrganiseUnitID'+ @Condition +' '+@SortBy)");
程序运行的时候@Condition的值是
where 1=1 and FMDS_WATER_IRRIGADRAINAGESTAT.ISDELETE = 0 AND FMDS_WATER_IRRIGADRAINAGESTAT.ORGANISEUNITID,
in (select OrganiseUnitID from SDMS_OrganiseUnit where OrganiseUnitCode
like '%'+(select OrganiseUnitCode from SDMS_OrganiseUnit where OrganiseUnitID=504)+'%'
and OrganiseUnitModel=1 and IsDelete=0) AND FMDS_WATER_IRRIGADRAINAGESTAT.ORGANISEUNITID = '504' AND FMDS_WATER_IRRIGADRAINAGESTAT.ISDELETE = 0
问题出在这里count(NULLIF(PumpModel,""))
如果我用双引号，报错：
以 ')) as cnt from FMDS_Water_IrrigaDrainageStat where 1=1 and FMDS_WATER_IRRIGADRAINAGESTAT.ISDELETE = 0 AND FMDS_WATER_IRRIGA' 开头的 标识符 太长。最大长度为 128。
单引号：报错
'504' 附近有语法错误。
关键字 'AND' 附近有语法错误。
------解决思路----------------------
简单来说就是把所有的NULLIF(PumpModel,"")换成NULLIF(PumpModel,‘’‘’)