SQL 2000: only select records where xp_fileexist = true?

Question

Hi,

imagine i have a table called tbl_pictures: picture_id and picture_name and there are about 500 records in that table. The pictures are stored in c:\mypics\

The problem: not all photos exists anymore, but they ARE still in the table. How can i list only those pictures that DO exist?

I do know how to check if a single file exists with "EXEC Master.dbo.xp_fileexist @filename", but i don't know how to do it in a loop.

It should look something like below:

SELECT picture_name FROM tbl_pictures WHERE (xp_fileexist '@picture_name' = true)

Anyone? :)

---

edit:

i've used an asp.net loop instead which calls a file-exists function. When it returns false the record is deleted from the table. Problem solved.

Answer 1

It's pretty hard to retrieve the results from a stored procedure. Basically you have to create a table that matches its exact column output, and insert ... exec into that. There's no chance at all to do that in a where clause.

You can, however, loop in a while, and them pump the results into a table. For example:

set nocount on
if OBJECT_ID('tempdb..#TempFileList') is not null
    drop table #TempFileList
create table #TempFileList (file_name nvarchar(250), file_exist bit)
insert #TempFileList (file_name) values ('c:\windows\win.ini')
insert #TempFileList (file_name) values ('c:\somefile.txt')

if OBJECT_ID('tempdb..#TempFileResult') is not null
    drop table #TempFileResult
create table #TempFileResult (File_exists int, File_directory int,parent_dir int)


declare c cursor local fast_forward for select file_name from #TempFileList
open c
declare @file_name nvarchar(250)
fetch from c into @file_name
while @@FETCH_STATUS = 0
    begin
    delete from #TempFileResult
    insert into #TempFileResult exec Master.dbo.xp_fileexist @file_name

    update  #TempFileList
    set     file_exist = (select file_exists from #TempFileResult)
    where   file_name = @file_name

    fetch from c into @file_name
    end

close c
deallocate c

select * from #TempFileList

On my system, this prints:

file_name             file_exist
c:\windows\win.ini    1
c:\somefile.txt       0