IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[usp_DirectoryExists]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[usp_DirectoryExists]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*
    DECLARE @pathName VARCHAR(255);
    SET @pathName =
        'C:\Windows';

    DECLARE @argStatusInd BIT;
    EXEC dbo.usp_DirectoryExists
        @argPathName = @pathName,
        @argStatusId = @argStatusInd OUTPUT
    SELECT @argStatusInd;
*/

/*******************************************************************************

    Procedure name: usp_DirectoryExists
    Date Modified:  05/24/2010
    Request #:
    Notes:          Simple utility SP that checks if a directory exists
                    in the FS. Returns 1 (true) if it does, returns 0 (false)
                    if not.

********************************************************************************/

CREATE PROCEDURE [dbo].[usp_DirectoryExists]
    @argPathName    VARCHAR(255),
    @argStatusId    BIT output
AS

SET NOCOUNT ON;

DECLARE @execCmd VARCHAR(2000);
DECLARE @result INT;

/*  Get a directory listing for a given path. It'll return an error
    if the directory doesn't exist, so we can use that to determine
    how to handle our returned value. */
SET @argStatusId = 1;
SET @execCmd = 'dir ' + @argPathName;

EXEC @result = master.dbo.xp_cmdshell @execCmd, no_output;

IF @result <> 0
BEGIN
    --PRINT 'Directory doesn''t exist.'
    SET @argStatusId = 0
    RETURN
END

SET NOCOUNT OFF;

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT  EXECUTE  ON [dbo].[usp_DirectoryExists] TO [public]
GO