問題描述
我有一個(gè)很長(zhǎng)的存儲(chǔ)過(guò)程，當(dāng)我執(zhí)行該過(guò)程時(shí)出現(xiàn)以下錯(cuò)誤:
I have a long stored procedure and when I execute the procedure I get the following error:
Msg 206, Level 16, State 2, Line 1
Operand type clash: varchar(max) is incompatible with sql_variant
所以為了解決問題，我已經(jīng)打印了問題所在，代碼是:
So to trouble shoot I have printed satetement where the problem is and the code is:
SELECT 'Name' ,
7 ,
CASE WHEN 'varchar' = 'varbinary'
THEN REPLACE(UPPER(sys.fn_sqlvarbasetostr([Name])), 'X', 'x')
ELSE CONVERT(VARCHAR(4000), [Name])
END , 'varchar'
FROM ref.dbo.datatables
WHERE id = 12
ORDER BY [ID]
所以當(dāng)我執(zhí)行上面的語(yǔ)句時(shí)，它給我的錯(cuò)誤是:
So When I execute the above statement it is givng me the error as:
Msg 206, Level 16, State 2, Line 1
Operand type clash: varchar(max) is incompatible with sql_variant
在ref.dbo.datatables表中Name的數(shù)據(jù)類型是Varchar(MAX)
The datatype of Name is Varchar(MAX) in ref.dbo.datatables table
如何解決這個(gè)問題?
答案:
這就是我所做的工作:
SELECT 'Name',
7,
CASE WHEN 'varchar' = 'varbinary'
THEN REPLACE(UPPER(sys.fn_sqlvarbasetostr(CONVERT(VARBINARY,[Name]))),'X','x')
ELSE CONVERT(VARCHAR(4000),[Name])
END,
'varchar'
FROM ref.dbo.datatables
WHERE id = 12
ORDER BY [ID]
推薦答案
錯(cuò)誤是正確的，您不能隱式(或顯式)將 VARCHAR(MAX) 強(qiáng)制轉(zhuǎn)換為 sql_variant.如果 Name 是 VARCHAR(MAX)，您需要將其轉(zhuǎn)換為兼容類型(如 VARCHAR(8000) 以傳遞它作為 sys.fn_sqlvarbasetostr()
The error is correct, you can't implicitly (or explicitly) cast a VARCHAR(MAX) to sql_variant. If Name is a VARCHAR(MAX) you will need to convert it to a compatible type (like VARCHAR(8000) in order to pass it in as a parameter to sys.fn_sqlvarbasetostr()
見msdn:
sql_variant 對(duì)象可以保存任何 SQL Server 數(shù)據(jù)類型的數(shù)據(jù)，除了 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml、timestamp 和 Microsoft .NET Framework 公共語(yǔ)言運(yùn)行時(shí) (CLR) 用戶定義的類型.sql_variant 數(shù)據(jù)的實(shí)例也不能將 sql_variant 作為其基礎(chǔ)數(shù)據(jù)類型.
sql_variant objects can hold data of any SQL Server data type except text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, timestamp, and Microsoft .NET Framework common language runtime (CLR) user-defined types. An instance of sql_variant data also cannot have sql_variant as its underlying base data type.
如果您需要 sys.fn_sqlvarbasetostr() 的功能并且無(wú)法在不丟失數(shù)據(jù)的情況下向下轉(zhuǎn)換您的 col，您可能需要推出您自己的該功能版本.CLR 將是一個(gè)不錯(cuò)的選擇.
If you need the functionality of sys.fn_sqlvarbasetostr() and can't down convert your col without losing data, you may need to roll your own version of that function. CLR would be a good bet.
這篇關(guān)于操作數(shù)類型沖突的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！