A few minor errors need to be addressed.
DECLARE @CODE varchar(20);
SET @sql = 'UPDATE Mymast'
SET @sql = @sql + 'SET img = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS Picture), SET PictureFileName = ' + @imagepath
SET @sql = @sql + 'WHERE CODE = ''' + @CODE + ''';'
EXECUTE sp_executesql @sql
EXEC (@sql);
sp_executesql (Transact-SQL):
http://msdn.microsoft.com/en-us/library/ms188001.aspx[
^]
I believe all the issues have been pointed out.
Consider using an alternative to a cursor as they are considered bad practice:
http://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server[
^]
Also possibly get in the habit of using the semicolon statement terminator:
Features Not Supported in a Future Version of SQL Server[
^]
DECLARE @CODE varchar(50);
DECLARE image_cursor CURSOR FOR
SELECT CODE FROM MyMast WHERE img IS NULL
OPEN image_cursor;
FETCH NEXT FROM image_cursor
INTO @CODE;
WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE @sql VARCHAR(MAX);
DECLARE @imagePath VARCHAR(255);
SET @imagePath = 'D:\images\' + RTRIM(LTRIM(@CODE)) + '.jpg';
SET @sql = 'UPDATE Mymast ';
SET @sql = @sql + 'SET img = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS Picture), PictureFileName = ''' + @imagepath + ''' ';
SET @sql = @sql + 'WHERE CODE = ''' + @CODE + ''';';
BEGIN TRY
EXEC (@sql);
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM image_cursor
INTO @CODE;
END;
CLOSE image_cursor;
DEALLOCATE image_cursor;
SELECT CODE, img FROM MyMast WHERE img IS NOT NULL;