IsNumeric with the error "A serious error occurred with the current command." SQL Server 2014 CTE

Question

I am running a series of scripts that generate a database. They end in SQL Server 2012 (11.0.5058.0). In SQL Server 2014 (12.0.4213.0) script errors with:

Msg 0, Level 11, State 0, Line 0
A serious error occurred in the current team. Results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0
A serious error occurred in the current team. Results, if any, should be discarded.

It seems that using the results of an IsNumeric expression inside a CTE query interrupts the query construction, since no string is required to generate an error. A collapsed version of the case I came across is:

 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 

The simplest case I can find is:

 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 

I checked the error levels from Microsoft , and it looks like 11 is a user error to fix, and 20 is a fatal error, so I feel like I get a mixed message.

Is there a right way to do this or is it a regression in 2014?

Answer 1

This is definitely a mistake.

There is no need for CTE to create this behavior. The expression below directly has the same effect.

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

I could reproduce 12.0.2269.0 and 12.0.4213.0, but not 12.0.4449.0, so now it is fixed.

Relevant KB article with details ( FIX: Access violation when a query uses ISDATE or ISNUMERIC functions in a connection condition in SQL Server 2014 SP1 ).

Stack trace when throwing exception below (for search)

 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,int,int,void *,char *) sqldk.dll!ex_raise_va_list(int,int,int,int,char *) sqllang.dll!alg_ex_raise(int,int,int,int,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 *,class CBindEnv *,int) sqllang.dll!COptExpr::BindTree(class CBindEnv *,int) sqllang.dll!CRelOp_QuerySpec::BindTree(class COptExpr *,class CBindEnv *,int) sqllang.dll!COptExpr::BindTree(class CBindEnv *,int) sqllang.dll!CRelOp_DerivedTable::BindTree(class COptExpr *,class CBindEnv *,int) sqllang.dll!COptExpr::BindTree(class CBindEnv *,int) sqllang.dll!CRelOp_Query::BindCTEList(class CBindEnv *,class COptExpr *) sqllang.dll!CRelOp_SelectQuery::BindTree(class COptExpr *,class CBindEnv *,int) sqllang.dll!COptExpr::BindTree(class CBindEnv *,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 CAlgStmt *,class CCompPlan *,bool,class CParamExchange *,unsigned long *) sqllang.dll!CSQLSource::FCompile(class CCompExecCtxt const &,class CParamExchange *) sqllang.dll!CSQLSource::FCompWrapper(class CCompExecCtxt const &,class CParamExchange *,enum CSQLSource::ESqlFunction) sqllang.dll!CSQLSource::Transform(class CCompExecCtxt const &,class CParamExchange *,enum CSQLSource::ESqlState) sqllang.dll!CSQLSource::Execute(class CCompExecCtxtBasic const &,class CParamExchange *,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() 

Answer 2

I think this is a mistake. However, I came up with a workaround that might work for you.

 ;WITH cte AS ( --SELECT -- CASE WHEN ISNUMERIC(t.CTECol) = 1 -- THEN 1 -- ELSE null --END as IsNCol1 SELECT CASE WHEN TRY_PARSE(t.CTECol AS INT) IS NOT NULL THEN 1 ELSE NULL END AS IsNCol1 FROM #Temp1 t ) SELECT * FROM #Temp2 JOIN cte ON #Temp2.NumCol = cte.IsNCol1 

TRY_PARSE returns NULL if the failure TRY_PARSE , so if it is NOT NULL , then you know it as a valid int.

There are a few subtle differences between the two functions, but I prefer TRY_PARSE anyway, because according to MSDN

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols, such as the dollar sign ($)

UPDATE:

I probably should clarify one of the subtle differences. ISNUMERIC returns 1 if the parameter can be parsed for any numeric type, and they are:

• int
• number
• bigint
• money
• smallint
• smallmoney
• tinyint
• float
• decimal
• real

This is not the same as TRY_PARSE , which is trying to parse the input into one of the above data types. (In my example, this is INT ). Therefore, if you really want to imitate ISNUMERIC , you will need to use a nested (or flattened) CASE for each type. Even then, the behavior may be somewhat unexpected, but this is a completely different story.