About Me

My photo
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Friday, October 21, 2011


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

CREATE PROC dbo.DoesPathExist
      @Path varchar(800),
      @Out bit = Null OUTPUT
/*    DATE              AUTHOR            REMARKS
      10/21/11          PPaiva            Initial creation.

            Handy proc for returning whether a path exists.  Output    
            parameter @Out returns 1 if it exists, 0 if it does not.

            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



      (FileExists bit,
       DirExists bit,
       ParentDirExists bit

      Exec master.dbo.xp_FileExist @Path

SELECT @Out = DirExists
FROM #FileExist