use Tempdb
go
--在SQL2005和SQL2008時，都會出現

set nocount on ;
if object_id('Tempdb..#A') is not null
    drop table #A

create table #A
(
    A_ID int  not null ,
    Type nvarchar(20)
)

if object_id('Tempdb..#B') is not null
    drop table #B

create 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           A
2           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，行 28
Error converting data type nvarchar to bigint.

*/

insert #B select 1,10,0--新增一條后，影響引擎類型判斷以下不會錯
go
select 
    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_Id


go


--新增20條數據,數據量記錄數對類型判斷有影響.

declare @i int
set @i=0
while @i<10
begin
    insert #B select 3,'Error'+rtrim(@i),2--類型非字符
    set @i=@i+1
end


go
--force order用提示強制執行順序

select 
    cast(b.value as int) as [轉換后正常]
from 
    #A a
Cross Apply
    (select top 1 Value from #B where A_ID=a.A_ID order by Num asc)b
option(force order)


drop table #a,#b