About Me

My photo
Colorado
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

DoesPathExist

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