Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
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.
Posted
Updated 11-Aug-22 21:17pm
v2

1 solution

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)
SQL
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))'

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 ...
SQL
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) +
')'

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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900