CODEWITHSUNDEEP
sql-servert-sql
Mike Petri
Mike Petri

Reputation: 605

Validate File Path on SQL Server

I am trying to validate a directory path before proceeding with downstream code. I have tried using TRY/CATCH with the code below, but it returns error code 3, which escapes TRY/CATCH. In T-SQL, how would one validate a file path?

BEGIN TRY
    EXEC master.dbo.xp_subdirs 'C:\Invalid Path'
END TRY
BEGIN CATCH
    SELECT 'Invalid Destination Location'
END CATCH

Upvotes: 0

Views: 1459

Answers (3)

Stu
Stu

Reputation: 32609

The error does not originate from SQL Server in this case and can't be caught in this manner.

A possible work-around is to check the path exists, at least for current versions of SQL Server:

declare @Exists table (Exist int, IsDirectory int, HasParent int) 

insert into @Exists exec master.dbo.xp_fileexist 'c:\invalid'
if exists (select * from @Exists where exist=1 and isdirectory=1)
begin
    select 'Folder exists'
end
else
begin
    select 'Invalid folder'
end

Upvotes: 0

Craig
Craig

Reputation: 1226

There are some exceptions to what SQL Server's TRY/CATCH will actually catch. Read towards the bottom of the reference - https://learn.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15 In your case, I believe your error is a severity level below 10, so will not be "caught"

The relevant section from SQL Server docs:

Errors Unaffected by a TRY...CATCH Construct TRY...CATCH constructs do not trap the following conditions:

Warnings or informational messages that have a severity of 10 or lower.

Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY...CATCH will handle the error.

Attentions, such as client-interrupt requests or broken client connections.

When the session is ended by a system administrator by using the KILL statement.

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY...CATCH construct:

Compile errors, such as syntax errors, that prevent a batch from running.

Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

Object name resolution errors

These errors are returned to the level that ran the batch, stored procedure, or trigger.

If an error occurs during compilation or statement-level recompilation at a lower execution level (for example, when executing sp_executesql or a user-defined stored procedure) inside the TRY block, the error occurs at a lower level than the TRY...CATCH construct and will be handled by the associated CATCH block.

Upvotes: 2

Steve Ford
Steve Ford

Reputation: 7753

An alternative mechanism is to use XP_FileExists undocumented stored procedure, note just like XP_SubDirs this system stored procedure may not exist in future versions of SQL Server:

CREATE TABLE #temp (FileExists int, IsDirectory int, ParentDirExists int)

INSERT INTO #temp
EXEC master..xp_fileexist 'C:\Invalid Path'

IF EXISTS(SELECT IsDirectory FROM #Temp WHERE IsDirectory = 1)
    PRINT 'Folder Exists'
ELSE
    PRINT 'Folder Doesnt Exists'
DROP TABLE #temp

Upvotes: 0

Related Questions