[怎樣處理]SQL2008、SQL2005類型判斷出錯解决方法

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 @[email protected]+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