First problem is that
is now a deprecated type and will eventually be removed, so avoid any future problems by replacing it with
Next, you need to find someway of getting @emp_image into a
format so that you can "add" it to your @SQL varchar (string). You then need to include the switch back to
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
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
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
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.