T-SQL Check whether a file exists

Question

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'

HABO · Accepted Answer · 2020-05-21 22:12:19Z

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.

Gordon Linoff · Accepted Answer · 2020-05-21 20:42:19Z

0

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.

answered May 21, 2020 at 20:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

HABO Over a year ago

Are you sure you want to concatenate the value of @file_exists? Shouldn't the variable be passed as an output parameter? And @filepath, though not as output?

Gordon Linoff Over a year ago

@HABO . . . I'm a little unclear on what the OP is really trying to do. This fixes the reported issue of +being interpreted (shockingly) as addition.

Collectives™ on Stack Overflow

T-SQL Check whether a file exists

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related