Dynamic SQL to load images from a directory

Question

Hi,

New to this and very stuck! I am trying to load images into a database from a directory using the following code.

When I execute the SP I get the following form the print statements I have included;

USE [store]
GO
DROP PROCEDURE [dbo].[insert2img]
/****** Object:  StoredProcedure [dbo].[insert2img]    Script Date: 07/08/2009 15:36:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create PROC [dbo].[insert2img]
as
Begin
   Declare @img1 as varbinary(max)
    Declare @dircmd as varchar(max)
             DECLARE @filename varchar(100)
  DECLARE @filepath varchar(100)
  DECLARE @maxRowID int
   DECLARE @count int
  DECLARE @tempXMLFileName table (RowId int identity(1,1), name varchar(100))
            Declare @sql as varchar(max)
        Set @count = 1 
        Set @dircmd = 'MASTER..XP_CMDSHELL "dir/b '+ 'C:\Images\*.*"'


    INSERT
      @tempXMLFileName exec (@dircmd)
        SET @maxRowID = (SELECT max(RowId) FROM @tempXMLFileName)
WHILE @count <= @maxRowID
 BEGIN
          SET @filename = (SELECT name FROM  @tempXMLFileName WHERE [RowId] = @count)
          Print @filepath
          Set @filepath = 'C:\Images\'+@filename
                Set @sql = 'Insert into dbo.aaaimg (sno,imgdate,imgname,img)
                            Select '' @count'',getdate(),''Image'',
                            BulkColumn from Openrowset(Bulk ''' + @filepath + ''',Single_Blob) as tt  '                       
        print @sql
        Print @count

        Exec (@sql)

        Set @filepath=' ' 
        print @filepath
        Set @count = @count + 1 

end
end

The output when I execute the SP with a few print statements is listed below;

Insert into dbo.aaaimg (sno,imgdate,imgname,img)
                            Select ' @count',getdate(),'Image',
                            BulkColumn from Openrowset(Bulk 'C:\Images\n0501.jpg', Single_Blob) as tt  
1

(1 row(s) affected)

My issue is that I am trying to insert the value of @count into the db, but instead I end up with the actual word @count in the db and not the value. I assume it is to do with the delimiters, but I can't get the combination correct.

Thanks,

James.

Answer 1

You'd need to concatenate the @count variable into the @sql variable like this:

Set @sql = 'Insert into dbo.aaaimg (sno,imgdate,imgname,img)
Select ' + CAST(@count AS VARCHAR) + ',getdate(),''Image'',
BulkColumn from Openrowset(Bulk ''' + @filepath + ''',Single_Blob) as tt '

Having said that I'd raise the point that it's not recommended storing images in SQL Server itself. Better to store the path of the image rather than the image itself in the db.