title: Dynamic SQL to load images from a directory
body: <p>New to this and very stuck! I am trying to load images into a database from a directory using the following code. </p>
  
  <p>When I execute the SP I get the following form the print statements I have included;</p>
  
  <pre><code>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 &lt;= @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
  </code></pre>
  
  <p>The output when I execute the SP with a few print statements is listed below;</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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.</p>
  
  <p>Thanks,</p>
  
  <p>James.</p>