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