c# - 在前端获取 "Top clause contains invalid value"错误，但在 SQL 数据中清晰可见

Question

将数据绑定(bind)到 Dataview 时出现此错误，但参数正确地向那里发送了值。

似乎 DBhelper 类抛出了这个错误，但不知道为什么

我的程序:

   insert #leads(opp_lead,opp_NAME,CNT) 
select  opp_lead,'LEAD'+opp_lead,COUNT(*) from #pipeline GROUP BY opp_lead
UPDATE #leads SET opp_NAME=CONTACT_NAME FROM TBLCONTACT C,  #leads L WHERE CONVERT(VARCHAR(50),L.opp_lead)=CONVERT(VARCHAR(50),C.CONTACT_ID) 
COUNT(*) DESC
    create table #YearlyEndCampaign
       (
        slno INT ,  
           lvl int        
       )
SELECT TOP 1 @MAXLEADS=COUNT(*) FROM #pipeline   GROUP BY opp_lead ORDER BY COUNT(*) DESC  

INSERT INTO #YearlyEndCampaign(slno,LVL)
    SELECT SEQ,0 FROM (SELECT TOP (@MAXLEADS) seq = ROW_NUMBER() OVER (ORDER BY number)  FROM [master]..spt_values)S

C#代码:

long businessID = Convert.ToInt64(Session["BusinessID"]);
long YEAR = Convert.ToInt64(2015);    
long UserID = Convert.ToInt64(Session["Contact_ID"]);

BeggingDA objBeggingDA = new BeggingDA();          
DataView dv = (new BeggingDA()).BeggingLoadByBusinessID(YEAR, businessID, UserID);

Answer 1

正如您所说，第一个 SELECT TOP 1 @MAXLEADS=COUNT(*) FROM #pipeline 语句没有返回任何数据。这意味着 @MAXLEADS 为空。

如果你随后运行 select top (NULL) FROM [master]..spt_values 你会得到 TOP clause contains an invalid value 错误，因为你没有传递TOP 的数值。

解决方案是在执行插入之前检查 @MAXLEADS 是否为 null - 如果没有任何行，您将选择前 0，因此无论如何执行插入都是没有意义的。一个简单的 IF @MAXLEADS IS NOT NULL 就足够了:

SELECT TOP 1 @MAXLEADS=COUNT(*) FROM #pipeline   GROUP BY opp_lead ORDER BY COUNT(*) DESC  

IF @MAXLEADS IS NOT NULL BEGIN

    INSERT INTO #YearlyEndCampaign(slno,LVL)
        SELECT SEQ,0 FROM (SELECT TOP (@MAXLEADS) seq = ROW_NUMBER() OVER (ORDER BY number)  FROM [master]..spt_values)S

END