February 19, 2016 at 1:02 am
Good day, i have a stored procedure that scans a folder structure to tell me which members on my system are missing contracts (.jpg files)
The sp contains subqueries like below since i need to scan other files as well.
For some reason the subquery is now telling my that files that do actually exist are missing (return 0 instead of 1)
select c.memberno, (select [mydb].[dbo].FileExists('C:\aaa\contracts\' + c.memberno +'.jpg')) Member_Contract from clubmembers c
however when i run the query outside of the main query (no subquery) the results are correct
select [mydb].[dbo].FileExists('C:\aaa\contracts\' + memberno +'.jpg') Member_Contract from clubmembers c
I am baffled, can anyone shed light on this situation. It would be greatly appreciated
February 19, 2016 at 2:58 am
Without knowing the sourcecode of the FileExists user-defined function, there is no way for us to tell you what's going on.
However, if it works for you without the subquery then the resolution is simple: don't use it in a subquery. In the code you posted, there is no good reason at all for the subquery.
Also, using T-SQL to test for existence of a file, and then even encapsulating that in a UDF, is a very bad use of SQL Server. Are you sure that you cannot use any other tools to test for existence of the file?
February 22, 2016 at 3:29 pm
Agree with Hugo, you're using probably the worst possible way for the task.
You better retrieve the list of all files in one go, using, say, xp_dirtree:
EXEC master.sys.xp_dirtree 'C:\aaa\contracts\', insert its output into a #table:
INSERT INTO #DirTree
EXEC master.sys.xp_dirtree 'C:\aaa\contracts\'
And then join #DirTree to the table from the query :
LEFT JOIN #DirTree DT ON DT.Subdirectory = 'C:\aaa\contracts\' + c.memberno + '.jpg' and DT.file = 1
In SELECT part you may use ISNULL(DT.file, 0) AS FileExists
Hope it helps.
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply