[怎樣處理]SQL2008、SQL2005類型判斷出錯

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!-- [if !mso]> <object classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui> </object> <mce:style><!-- st1/:*{behavior:url(#ieooui) } --> <!-- [endif]--> <!-- [if gte mso 10]> <mce:style><!-- /* Style Definitions */ table.MsoNormalTable {mso-style-name:表格內文; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} --> <!-- [endif]-->

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

-- 怎樣處理方法 ( 用表提示處理 , 改變數據庫引擎的執行順序 )

--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

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

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