SQL服务器中的转换错误“数据类型nvarchar（max）和varbinary（max）在add运算符中不兼容” [英] Conversion error in SQL server "the data types nvarchar(max) and varbinary(max) are incompatible in the add operator"

问题描述

我在动态查询中对变量使用varbinary类型转换。我很好地处理动态查询错误，并且在动态查询中转换nvarchar（max）和varbinary（max）时我知道出了什么问题。

但我无法找到它的解决方案。

I am using a varbinary type conversion on a variable in a dynamic query. I deal with dynamic query errors very well, and I know something is wrong while converting nvarchar(max) and varbinary(max) in a dynamic query.
But I am not able to find its solution.

DECLARE @Oldlogo	varbinary(max)
SET @Oldlogo = 'some binary code'
declare @query nvarchar(max)
SET @query = ''
SET @query = @query + '  IF EXISTS(SELECT 1 FROM  Company WHERE CONVERT(varbinary,Company_Logo_TopLeft) = CONVERT(varbinary,''' + @Oldlogo + ''')) '
	SET @query = @query + '  BEGIN '
	SET @query = @query + ' Do someting'	
       SET @query = @query + '   END '
SET @query = @query + '   ELSE'
       SET @query = @query + '  BEGIN '
	SET @query = @query + ' Do someting else'	
	SET @query = @query + '   END '
EXEC (@query)

错误信息是

数据类型nvarchar（max）和varbinary（max）在add运算符中不兼容。

任何帮助？

谢谢



我尝试了什么：



我尝试使用参数查询并使用sp_executesql执行它但没有运气。使用sp_executeSQL和@params

Error message is
"The data types nvarchar(max) and varbinary(max) are incompatible in the add operator."
Any help?
Thanks

What I have tried:

I tried using parameter query and execute it with sp_executesql but no luck. used sp_executeSQL and @params

推荐答案

当你这样写：

When you write this:

DECLARE @Oldlogo	varbinary(max)
... CONVERT(varbinary,Company_Logo_TopLeft) = CONVERT(varbinary,''' + @Oldlogo + ''')) '

您正尝试通过CONVERT函数将VARBINARY值（从 @OldLogo ）转换为VARCHAR（作为字符串连接的一部分），然后将其转换回VARBINARY） ）



您无法将VARBINARY转换为VARCHAR - 这种测试确实需要在您的表示层而不是SQL中完成 - d将二进制值连接成一个字符串（即使它工作）是一个SQL注入的邀请。

你为什么要尝试这样做动态查询？

你可以将@OldLogo和Company_Logo_TopLeft与一个简单的'='进行比较，而不会产生任何动态。

You are trying to convert a VARBINARY value (from @OldLogo) to a VARCHAR (as part of your string concatenation) and then convert it back to a VARBINARY) via the CONVERT function)

You can't convert VARBINARY to VARCHAR - that kind of testing really needs to be done in your presentation layer rather than in SQL - and concatenating the binary value into a string (even if it worked) is an invitation for SQL Injection.
Why are you trying to do that as a dynamic query?
You could compare @OldLogo and Company_Logo_TopLeft with a simple '=' without making that dynamic at all.

首先解决第一个问题：

Quote：

不允许从数据类型varchar到varbinary（max）的隐式转换。使用CONVERT函数运行此查询。

Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query."

我这样做：

SET @Oldlogo = convert(varbinary,'some binary code')

您在问题中引用的问题是由以下原因引起的：

The problem you quote in your question is caused by :

SET @query = @query + '  IF EXISTS(SELECT 1 FROM  Company WHERE CONVERT(varbinary,Company_Logo_TopLeft) = CONVERT(varbinary,''' + @Oldlogo + ''')) '

目前尚不清楚为什么要插入某些二进制代码并再次将其转换为varbinary。 br />
Onc你已经整理出你实际想要做的事情，用

It is not clear why you are trying to insert 'some binary code' and convert it to varbinary again.
Once you've sorted out what you are actually trying to do, replace

EXEC (@query)

with 

PRINT @query

PRINT @query

直到你完全满意你生成的SQL是正确的。



从一组特定数据开始并做原始SQL语句是个好主意可以解决如何构建动态SQL - 即不要直接潜入

until you are completely happy that the SQL you are generating is correct.

It's a good idea to start with a specific set of data and do raw SQL statements so that you can work out how to build the dynamic SQL - i.e. don't just dive straight in

这篇关于SQL服务器中的转换错误“数据类型nvarchar（max）和varbinary（max）在add运算符中不兼容”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！