0

I have a recursive delete stored procedure that removes all files and folders from my records when I delete a file or folder. However, I also have a file directory where the actual files are stored. These file names are stored in the records that are to be deleted. How would I manage retrieving the filename from the record about to be deleted and deleting that file from the directory as well?

Here is my procedure as it stands:

WHILE (SELECT COUNT(FileID) FROM Files WHERE ParentFolderID is not null AND ParentFolderID not in (SELECT FileID FROM Files) AND ParentfolderID !=0 ) > 0
BEGIN
DELETE FROM Files Where ParentFolderID is not null AND ParentFolderID !=0 AND ParentFolderID not in (SELECT FileID FROM Files);
END

The identifier in Files used in retrieving a file from the directory is "FileName". Long story short, while deleting "X" from Files, get filename of "X" and delete from file directory too.

Krunal Patil
  • 3,666
  • 5
  • 22
  • 28
Chen_Dogg
  • 91
  • 1
  • 12
  • This is just a long shot but if you have a microsoft sql server you could try to create a CLR User Defined function. This function takes the path of the filename as a parameter and deletes it. Then you call this function before your DELETE FROM. – deblendewim May 21 '14 at 11:57

1 Answers1

0

SQL CLR functions are the way to go. This article shows an example on how to delete a file using a CLR function: http://www.mssqltips.com/sqlservertip/2302/extending-file-system-operations-in-sql-server-using-clr/

deblendewim
  • 425
  • 3
  • 12
  • CLR seems like the long way round... If I were to use xp_cmdshell how would I insert that in the above code? – Chen_Dogg May 26 '14 at 12:12
  • correct me if I'm wrong, but the WHILE loop above will only run once. If you were to add a xp_cmdshell, you need to delete all your files in one shot. The DEL command of windows allows this if you concatenate the file names. Use this help (http://stackoverflow.com/questions/887628/convert-multiple-rows-into-one-with-comma-as-separator) to concatenate all your file names and append it to a xp_cmdshell /Q 'del '. The /Q parameter to do a silent delete. – deblendewim May 27 '14 at 12:22