在前端获得“Top 子句包含无效值”的错误,但在 SQL 数据中清晰可见
本文关键字:错误 但在 SQL 清晰可见 数据 前端 Top 无效 包含 子句 | 更新日期: 2023-09-27 18:30:58
将数据绑定到数据视图时收到此错误,但参数正确发送值。
似乎 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);
正如您所说,第一个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