Handy procedure for returning whether a given path exists.
It simply calls out the extended procedure xp_FileExist. Normally it would be appropriate to have this in a user-defined function (UDF), but since UDFs in SQL don’t permit calling extended procedures, it is done here in a stored procedure. You can either check the OUTPUT parameter @Out or check for the return value. A value of 1 indicates the path exists, a 0 indicates it does not exist.
I call this proc from other code, just to make the code fail-safe. You never know when someone changes a path or directory structure; much better to be prepared.
USE Admin
GO
CREATE PROC dbo.DoesPathExist
@Path varchar(800),
@Out bit = Null OUTPUT
AS
/* DATE AUTHOR REMARKS
10/21/11 PPaiva Initial creation.
DESCRIPTION
Handy proc for returning whether a path exists. Output
parameter @Out returns 1 if it exists, 0 if it does not.
USAGE
Check the output parameter:
-- This path probably exists
DECLARE @Out bit
Exec DoesPathExist 'C:\Program Files', @Out OUTPUT
SELECT @Out
-- This path doesn't exist
DECLARE @Out bit
Exec DoesPathExist 'C:\blah', @Out OUTPUT
SELECT @Out
OR, check the return value:
-- This path probably exists
DECLARE @ReturnVal int
Exec @ReturnVal = DoesPathExist 'C:\Program Files'
SELECT @ReturnVal
-- This path doesn't exist
DECLARE @ReturnVal int
Exec @ReturnVal = DoesPathExist 'C:\blah'
SELECT @ReturnVal
DEPENDENCY
xp_FileExist
*/
CREATE TABLE #FileExist
(FileExists bit,
DirExists bit,
ParentDirExists bit
)
INSERT INTO #FileExist
Exec master.dbo.xp_FileExist @Path
SELECT @Out = DirExists
FROM #FileExist
RETURN @Out