use Tempdbgo--在SQL2005和SQL2008時，都會出現set nocount on ;if object_id('Tempdb..#A') is not null drop table #Acreate table #A( A_ID int not null , Type nvarchar(20))if object_id('Tempdb..#B') is not null drop table #Bcreate table #B( A_ID int not null, Value nvarchar(4000), Num int--無意義列)insert #A select 1,N'A'insert #A select 2,N'A'insert #B select 3,'Error',0--類型非字符/*#A:A_ID Type----------- --------------------1 A2 A#B:A_ID Value Num----------- -------------------- -----------3 Error 0*/--轉換后出錯時出錯,不成立沒結果集select cast(b.value as bigint) as [轉換后出錯]from #A a inner join #B b on a.A_Id=b.A_Id/*訊息 8114，層級 16，狀態 5，行 28Error converting data type nvarchar to bigint.*/insert #B select 1,10,0--新增一條后，影響引擎類型判斷以下不會錯goselect cast(b.value as bigint) as [轉換后出錯]from #A a inner join #B b on a.A_Id=b.A_Id--怎樣處理方法1(用表提示處理,改變數據庫引擎的執行順序)--LOOP | HASH | MERGE | REMOTE delete #B where isnumeric(Value)=1--刪除新增記錄select cast(b.value as int) as [轉換后正常]from #A a inner HASH join #B b on a.A_Id=b.A_Idgo--新增20條數據,數據量記錄數對類型判斷有影響.declare @i intset @i=0while @i<10begin insert #B select 3,'Error'+rtrim(@i),2--類型非字符 set @i=@i+1endgo--force order用提示強制執行順序select cast(b.value as int) as [轉換后正常]from #A aCross Apply (select top 1 Value from #B where A_ID=a.A_ID order by Num asc)boption(force order)drop table #a,#b
用起来感觉还凑合，不用在句子里费劲调整isnumeric的位置，也基本不用担心引擎的升级。 而且执行效率往往不比一个过于复杂的句子低。 [解决办法] 1.同意31楼的看法，不到万不得已不用Query/Table hint 2.遇到过类似情况（转Datetime） 3. select CASE WHEN ISNUMERIC(t.MyVal) = 1 THEN cast(t.MyVal as decimal(16,2)) ELSE NULL END from #A a cross apply ( select top 1 b.MyVal from #B b where b.A_ID=a.AID order by b.Sort desc )t where a.ItemType=0
