0

Can you please help me to give the logic for finding folders in specified drive. As an example below:

Declare @NewDrive varchar(1)
Set @NewDrive = 'C'
DECLARE @StringValue varchar(2000)
SET @StringValue = 'C:\Drive1\Folder1\db3.ndf'

I need to find whether folders in @StringValue exist in @NewDrive or not. If not should create it.

@StringValue can have many folders.

Madhu_sms
  • 1
  • 1
  • 1
  • 2
    possible duplicate of [How do I check if a directory exists using SQL Server?](http://stackoverflow.com/questions/13765911/how-do-i-check-if-a-directory-exists-using-sql-server) – Cristik May 05 '15 at 05:17

1 Answers1

0

I don't think that you have an in-built function for it, but I found a way through this LINK, that is by creating a function;

CREATE FUNCTION dbo.fn_FileExists(@path varchar(512))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

Once the function is created, you can just proceed as follows;

Declare @cmdpath nvarchar(60)
If Not EXISTS (select dbo.fn_FileExists(filename) from dbo.YourTable)
Begin

 // Create folder
 exec master.dbo.xp_cmdshell @cmdpath

End

Hope it helps.

Community
  • 1
  • 1
Nadeem_MK
  • 7,533
  • 7
  • 50
  • 61
  • You can not call procedures in functions. It's because functions can't change anything, they are read-only access. – TcKs May 05 '15 at 07:33