sql-server – IsNumeric失败,出现“当前命令发生严重错误.”SQL Server 2014 CTE

我正在运行一系列生成数据库的脚本.它们在sql Server 2012(11.0.5058.0)上运行完成.在sql Server 2014(12.0.4213.0)上,脚本错误：

Msg 0,Level 11,State 0,Line 0
A severe error occurred on the current command. The results,if any,should be discarded.

Msg 0,Level 20,should be discarded.

看来,在CTE查询中使用IsNumeric语句的结果会破坏查询构建,因为不需要任何行来导致错误.我遇到的案例的简化版本是：

CREATE TABLE #Temp1 ( CTECol VARCHAR );
CREATE TABLE #Temp2 ( NumCol Int null);
;
WITH cte AS 
(
    SELECT 
        CASE WHEN ISNUMERIC(t.CTECol) = 1 
                THEN 1 
                ELSE null
        END as IsNCol1
    FROM
        #Temp1 t
)
SELECT * 
FROM #Temp2 
JOIN cte ON #Temp2.NumCol = cte.IsNCol1

我能找到的最简单的案例是：

CREATE TABLE #Temp3 ( CTECol Int );
CREATE TABLE #Temp4 ( NumCol Int );
;
WITH cte AS 
(
    SELECT ISNUMERIC(t.CTECol) as IsNCol1
    FROM #Temp3 t
)
SELECT * 
FROM #Temp4 
JOIN cte ON #Temp4.NumCol = cte.IsNCol1

我检查了Microsoft的错误级别,看起来11是可纠正的用户错误,20是致命错误所以我觉得我收到的是混合消息.

有没有正确的方法来做到这一点,还是在2014年回归？

解决方法

这当然是一个错误.

CTE也不需要产生这种行为.下面直接使用表达式具有相同的效果.

SELECT *
FROM   #Temp4
       JOIN #Temp3
         ON #Temp4.NumCol = ISNUMERIC(#Temp3.CTECol)

我可以在12.0.2269.0和12.0.4213.0但不是12.0.4449.0上进行复制,所以看起来它现在已经修复了.

抛出异常时的堆栈跟踪低于(可搜索性)

KernelBase.dll!RaiseException()
msvcr100.dll!_CxxThrowException(void * pExceptionObject,const _s__ThrowInfo * pThrowInfo) Line 157
sqldk.dll!ExceptionBackout::GetCurrentException(void)
sqldk.dll!ex_raise2(int,int,void *,char *)
sqldk.dll!ex_raise_va_list(int,char *)
sqllang.dll!alg_ex_raise(int,...)
sqllang.dll!CAlgTableMetadata::RaiseBadTableException(int,int)
sqllang.dll!CAlgTableMetadata::Bind(class CRelOp_Query *,class COptExpr *)
sqllang.dll!CRelOp_Get::BindTree(class COptExpr *,class CBindEnv *,int)
sqllang.dll!COptExpr::BindTree(class CBindEnv *,int)
sqllang.dll!CRelOp_FromList::BindTree(class COptExpr *,int)
sqllang.dll!CRelOp_QuerySpec::BindTree(class COptExpr *,int)
sqllang.dll!CRelOp_DerivedTable::BindTree(class COptExpr *,int)
sqllang.dll!CRelOp_Query::BindCTEList(class CBindEnv *,class COptExpr *)
sqllang.dll!CRelOp_SelectQuery::BindTree(class COptExpr *,int)
sqllang.dll!CRelOp_Query::FAlgebrizeQuery(class COptExpr *,class CCompExecCtxtStmt const &,enum EObjType,class CSequenceProjectContext *)
sqllang.dll!CProchdr::FNormQuery(class CCompExecCtxtStmt const &,class CAlgStmt *,enum EObjType)
sqllang.dll!CProchdr::FNormalizeStep(class CCompExecCtxtStmt const &,class CCompPlan *,bool,class CParamExchange *,unsigned long *)
sqllang.dll!CsqlSource::FCompile(class CCompExecCtxt const &,class CParamExchange *)
sqllang.dll!CsqlSource::FCompWrapper(class CCompExecCtxt const &,enum CsqlSource::EsqlFunction)
sqllang.dll!CsqlSource::Transform(class CCompExecCtxt const &,enum CsqlSource::EsqlState)
sqllang.dll!CsqlSource::Execute(class CCompExecCtxtBasic const &,unsigned long)
sqllang.dll!process_request(class IBatch *,class SNI_Conn *,enum RequestType)
sqllang.dll!process_commands(void *)
sqldk.dll!SOS_Task::Param::Execute(class SOS_Task *,void * * const)
sqldk.dll!SOS_Scheduler::RunTask(class Worker *)
sqldk.dll!SOS_Scheduler::ProcessTasks(class SOS_Scheduler *,class Worker *)
sqldk.dll!SchedulerManager::WorkerEntryPoint(class Worker *)
sqldk.dll!SystemThread::RunWorker(class Worker *)
sqldk.dll!SystemThreadDispatcher::ProcessWorker(class SystemThread *)
sqldk.dll!SchedulerManager::ThreadEntryPoint(void *)
kernel32.dll!BaseThreadInitThunk()
ntdll.dll!RtlUserThreadStart()

原文链接：https://www.f2er.com/mssql/78132.html

sql-server – IsNumeric失败,出现“当前命令发生严重错误.”SQL Server 2014 CTE

解决方法

猜你在找的MsSQL相关文章