Renumber or rename files in a folder

SCENARIO
You take photos on your camera, they are named something similar to:
    DSC_0001.nef
    DSC_0002.nef
    ...
    DSC_0421.nef

During your photoshoot, your memory card becomes full so you swap cards.  After your photoshoot your new card has photos with these names:
    DSC_0001.nef
    DSC_0002.nef
    ...
    DSC_0141.nef

You want to rename the second batch of files to be renumbered, starting from 0422 ending at 0562.

I'm not sure what other photographers do, but since I also wear an SQL hat, I simply created this proc:

USE Admin

IF OBJECT_ID('dbo.cameraFileRenumber') Is Not Null

       DROP PROC dbo.cameraFileRenumber

go

CREATE PROC dbo.cameraFileRenumber

       @Path varchar(500),

       @Base varchar(30),

       @NumDigits int,

       @OffsetAdd int,

       @Debug bit = 1

AS

/*     DATE          AUTHOR        REMARKS

       1/1/17        PPaiva        Initial creation.

       DESCRIPTION

              Renumbers camera files with a numeric offset.

       SCENARIO

              You do a photoshoot with a memory card.  Photos are numbered as such:

                     DSC_0001.nef    to   DSC_0421.nef  (421 files)

              Your memory card fills up.  You change memory cards, the new photos are now numbered:

                     DSC_0001.nef    to   DSC_0141.nef  (141 files)

              You have a problem - you can't put all the files into the same folder because they

                     start counting from the same base number.  Therefore you have overlapping file names.

              Solution:  Rename the second set of files to start counting at 0422. 

              Desired end result:  DSC_0001.nef to     DSC_0562.nef  (562 files)

       USAGE

              -- Debug

              cameraFileRenumber

                     'K:\Images\Nikon\2017\2017-12-17_Elizabeth\batch2',

                     'DSC_', 4, 421

                     , 0

              -- Execute

              cameraFileRenumber 'K:\Images\Nikon\2017\2017-12-17_Elizabeth\batch2', 'DSC_', 422

       DEBUG

              xp_fixeddrives

              xp_cmdshell '

*/

SET NOCOUNT ON

DECLARE @s varchar(1000)

SET @s = 'dir /b "' + @Path + '"'

CREATE TABLE #t (

       ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,

       Filename varchar(30),

       FilenameNoExt varchar(30),

       Extension varchar(10),

       sNumWithoutBase varchar(10),

       NumWithoutBase int,

       NewNumWithoutBase int,

       NewFilename varchar(30),

       RenameCommand varchar(1100)

       )

SET @Path = dbo.udfAddBackslashIfNec(@Path)

-- Get filenames into table

INSERT INTO #t (Filename)

       Exec xp_cmdshell @s

DELETE

FROM #t

WHERE Filename Is Null

IF @Debug = 1

       SELECT  Filename,

                     CHARINDEX('.', Filename) Pos,

                     Left(Filename, CHARINDEX('.', Filename) - 1) FilenameNoExt,

                     Substring(Filename, CHARINDEX('.', Filename) + 1, 100 ) ExtOnly

       FROM #t

UPDATE #t

SET FilenameNoExt = Left(Filename, CHARINDEX('.', Filename) - 1),

       Extension = Substring(Filename, CHARINDEX('.', Filename) + 1, 100 )

UPDATE #t

SET    sNumWithoutBase = Replace(FilenameNoExt, @Base, '')

UPDATE #t

SET NumWithoutBase = convert(int, sNumWithoutBase)

      

UPDATE #t

SET NewNumWithoutBase = NumWithoutBase + @OffsetAdd

UPDATE #t

SET NewFilename = @Base + dbo.udfPadLeft(NewNumWithoutBase, @NumDigits, '0') + '.' + Extension

UPDATE #t

SET RenameCommand = 'rename "' + @Path + Filename + '" "' + NewFilename + '"'

IF @Debug = 1

       SELECT  *

       FROM #t

       ORDER BY ID

DECLARE @MaxID int,

              @ID int,

              @Cmd varchar(1100)

SELECT @MaxID = Max(ID),

              @ID = 0

FROM #t

WHILE @ID < @MaxID

       BEGIN

              -- Get next ID

              SELECT @ID = Min(ID)

              FROM #t

              WHERE ID > @ID

              -- Get other attribs

              SELECT @Cmd = RenameCommand

              FROM #t

              WHERE ID = @ID

              IF @Debug = 0

                     Exec xp_cmdshell @cmd

              Print @cmd

       END