Some other things to improve on the code might be:

Get rid of the return statement after the raiserror since it can never be hit when raising severity level 16

if @db_name is null

begin

raiserror ('error: db name is null',16,1)

return

end

Maybe add an entire TRY/CATCH block to the whole procedure instead of just at the end to deal with this appropriately? And nest the existing one inside of it.

Find out if you need to increase the size of this one it is pretty small and I think a directory path can be up 260:

@snap_dir varchar(100) = 'c:\temp\backup'

note there would be issues with DB names greater than 100 characters since they could be up to nvarchar(128) and the snapshot name seems to limit things even further. Also a potential for filename to have problems with these sizes:

@snapshot_name varchar(50)

@filename varchar(100)

And look at possibly replacing the cursor. There are plenty of examples of how to do so in the forum.