SQL Stored Procedure - Find if a .txt file exists using wildcard
Posted on 2007-12-06
Does anyone here knows how to check whether if any files with a particular extension exists in a folder?
I'm building a stored procedure (SQL Server 2000) where I'm supposed to find any files with the .txt extension in one of my hard drive folders. Here's the code I'm using:
DECLARE @Var varchar(200)
DECLARE @WildCard varchar (50)
SET @WildCard = 'MyLostFile'
SET @Var= 'C:\MyFolder\' + @wildcard + '.txt'
EXEC master..xp_fileexist @var, @return OUTPUT
IF @return =1
PRINT'File does exist '
PRINT 'File does not exists '
This works like a charm if I provide the actual name of the text file in the wildcard variable. However, I am not supposed to know it. I need the wildcard variable to be a truly wild card like '%' and then I'm supposed to look in my folder for any files with the .txt extension.
If anyone knows how to accomplish this, please share it.