Replace Image Path
This will replace the photo directory location that is set at @pathPartToReplace on line 2 with the directory path located at @replacementPathPart on line 5.
Once you run the script to verify that the paths look correct, remove the comments (--) from the UPDATE, SET, and WHERE lines on 29-31.
DECLARE @pathPartToReplace varchar(128)
SET @pathPartToReplace = 'C:\ProgramData\BadgePass\Photos\'
DECLARE @replacementPathPart varchar(128)
SET @replacementPathPart = 'C:\Foo\Bar\'
DECLARE @entityImageID int
DECLARE @path varchar(128)
DECLARE @newPath varchar(128)
DECLARE imageCursor CURSOR FOR
SELECT [entityImageID], [path]
FROM EntityImage
WHERE path LIKE @pathPartToReplace + '%'
OPEN imageCursor
FETCH NEXT FROM imageCursor
INTO @entityImageID, @path
WHILE @@FETCH_STATUS = 0
BEGIN
SET @newPath = @replacementPathPart + SUBSTRING(@path, LEN(@pathPartToReplace) + 1, LEN(@path) - LEN(@pathPartToReplace))
PRINT @path
PRINT @newPath
--UPDATE [dbo].[EntityImage]
-- SET [path] = @newPath
-- WHERE [entityImageID] = @entityImageID
FETCH NEXT FROM imageCursor
INTO @entityImageID, @path
END
CLOSE imageCursor
DEALLOCATE imageCursor