消息402，级别16，状态1，过程测试，行25数据类型varchar和image在add运算符中不兼容。 [英] Msg 402, Level 16, State 1, Procedure test, Line 25 The data types varchar and image are incompatible in the add operator.

问题描述

alter  procedure test 
 (
 @t1 varchar(50),

@emp_id varchar(15),

@emp_fathername varchar(15),
@emp_mothername varchar(15),
@emp_spousename varchar(15),
@emp_dob date,
@emp_gender varchar(10),
@emp_martialstatus varchar(10), 
@emp_qual1 varchar(15),
@emp_qual2 varchar(15),
@emp_image image,
@emp_bldgroup varchar(5)

)
 as
 begin
 
 DECLARE @SQL varchar(250)
 
 SELECT @SQL = 'insert into  ' + @t1 + ' VALUES('''+@emp_id+''','''+@emp_fathername+''','''+@emp_mothername+''','''+@emp_spousename+''','''+Cast(@emp_dob as varchar(10))+''','''+@emp_gender+''','''+@emp_martialstatus+''','''+@emp_qual1+''','''+@emp_qual2+''','''+@emp_image+''','''+@emp_bldgroup+''')'
 
 EXEC (@SQL)
   
   print @SQL
    
 end 

错误

ERROR

Msg 402, Level 16, State 1, Procedure test, Line 25
The data types varchar and image are incompatible in the add operator.

推荐答案

第一个问题是 image 现在是不推荐使用的类型，最终将被删除，因此请将其替换为 varbinary（m）以避免将来出现任何问题ax）。



接下来，您需要找到将@emp_image转换为 varchar 格式，以便您可以将其添加到@SQL varchar（字符串）。然后，您需要在正在构建的动态SQL中包含切换回 varbinary 。



Something这样应该有效（CAVEAT！我还没能完全检查这个）



First problem is that image is now a deprecated type and will eventually be removed, so avoid any future problems by replacing it with varbinary(max).

Next, you need to find someway of getting @emp_image into a varchar format so that you can "add" it to your @SQL varchar (string). You then need to include the switch back to varbinary within the dynamic SQL that you are building up.

Something like this should work (CAVEAT! I have not been able to fully check this)

DECLARE @imageAsVChar VARCHAR(MAX);
SET @imageAsVChar = @emp_image

DECLARE @backToVBin VARCHAR(MAX);
SET @backToVBin = 'DECLARE @workimage VARBINARY(MAX) = CAST(' + CHAR(39) + @dataCHAR + CHAR(39) + ' AS VARBINARY(MAX))'

因为我们要在实际的动态sql中包含 @backToVBin ，所以我们将@workmage的引用包含为文本而不是作为一个变量...像这样...

Because we are going to include @backToVBin in the actual dynamic sql we include the reference to "@workmage" as text rather than as a variable ... like this ...

SELECT @SQL = @backToVBin +
' insert into ' + @t1 + ' VALUES(' +
CHAR(39) + @emp_id + CHAR(39) +
',' + CHAR(39) + @emp_fathername + CHAR(39) +
',' + CHAR(39) + @emp_mothername + CHAR(39) +
',' + CHAR(39) + @emp_spousename + CHAR(39) +
',' + CHAR(39) + Cast(@emp_dob as varchar(10)) + CHAR(39) +
',' + CHAR(39) + @emp_gender + CHAR(39) +
',' + CHAR(39) + @emp_martialstatus + CHAR(39) +
',' + CHAR(39) + @emp_qual1 + CHAR(39) +
',' + CHAR(39) + @emp_qual2 + CHAR(39) +
', @workimage' +
',' + CHAR(39) + @emp_bldgroup + CHAR(39) +
')'

而不是使用多个单引号'''（可能会非常混乱）我使用了 CHAR （39）表示围绕文本所需的单引号。我推荐这是一个很好的方法来继续使用。



正如我之前所说的，我无法对此进行全面测试。如果遇到问题，首先要做的是将 print @SQL 移到EXEC之前。如果你检查那个输出，那么应该很容易发现错误的地方 - 或者将它粘贴在评论中，我会再看看。



说了这么多，我个人的偏好根本不是将图像存储在数据库中 - 我更喜欢将它们存储在专用的图像存储库（例如FileNet及其版本）中或存储在磁盘上的文件中。然后，我只存储对数据库中单个图像的引用。

Instead of using multiple single quotes ''' (which can get very messy) I've used CHAR(39) to represent the single quotes you need to surround the text. I recommend this as a good approach to use going forward.

As I said earlier, I haven't been able to fully test this. If you get problems the first thing to do is move the print @SQL to before the EXEC. If you examine that output it should be reasonably easy to spot what's wrong - or paste it in a comment here and I'll have another look.

Having said all that, my personal preference is not to store images in the database at all - I prefer to store them either in a dedicated image repository (e.g. FileNet and its contempories) or as files on disk. I then store just a reference to the individual image on the database.

这篇关于消息402，级别16，状态1，过程测试，行25数据类型varchar和image在add运算符中不兼容。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！