CODEWITHSUNDEEP
sqlsql-servert-sql
Stanley Onchari
Stanley Onchari

Reputation: 3

T-SQL Check whether a file exists

Am trying to check whether a file exists, and store the result in a variable. Am expecting the variable @file_exists to be either true or false, but am getting the error:

Conversion failed when converting the nvarchar value 'xp_fileexist C:\ABC\myfile_20200521.xls, ' to data type int

What is it that am doing wrong?

DECLARE @filepath VARCHAR(MAX), 
        @file_exists INT = 0, 
        @sql VARCHAR(MAX);

SET @filepath = (SELECT CONCAT('C:\ABC\myfile_',convert(varchar,getdate(),112),'.xls'));

SET @sql = N'xp_fileexist '+@filepath+ N', '+@file_exists+ N' OUT' 
EXEC sp_executesql @sql

IF(@file_exists = 1)
    PRINT 'File exists'

Upvotes: 0

Views: 4717

Answers (2)

HABO
HABO

Reputation: 15816

You have run into a case of data type precedence. The statement

SET @sql = N'xp_fileexist '+@filepath+ N', '+@file_exists+ N' OUT'

contains @file_exists which is declared as an INT, therefore all of the strings need to be converted to INT values for addition.

What you appear to want is:

declare @FilePath as NVarChar(256), @FileExists as Int = 0;

set @FilePath = Concat( N'C:\ABC\myfile_', Convert( NVarChar(8), GetDate(), 112), N'.xls' );

execute master.dbo.xp_fileexist @FilePath, @FileExists output;

if ( @FileExists = 1 )
    print 'File exists.'

Note that the data type specified in the Convert has a length.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The simplest solution is to use concat() the function:

SET @sql = CONCAT(N'xp_fileexist ', @filepath, N', ', @file_exists, N' OUT')

That way, + won't be interpreted as a string.

You could also pass in the values as parameters, which would be recommeneded.

Upvotes: 0

Related Questions