Putting a path in a where statement

Transact-SQL
1

Hi all. I'm trying to retrieve ID Codes of employees that have we have no ID Picture saved yet. The ID Pictures' file names are based on an Employee's ID Code. Can I put a path in a where statement in SQL in order to do this?

This is what I thought of at first but I know it's incorrect: where ('C:\Portal\Images\EmployeePhotos' + EmployeeCode + '.jpg')
I just hope it gives you a clear idea of what I am trying to accomplish here.

Thanks!

2

You need to compare your path/name to something. The WHERE clause is looking for true or false (Boolean) expression. WHERE
My brain is not working this morning so my NOT EXISTS example is not coming to mind.

1 Like
3

You can use master.sys.xp_fileexist to check if a file exists. Probably easiest / most reuseable to create a function to do it:

CREATE FUNCTION dbo.CheckFileExists (
    @filename varchar(255)
    )
RETURNS bit
AS
BEGIN
DECLARE @FileExists int
EXEC Master.dbo.xp_fileexist @filename, @FileExists OUTPUT
RETURN @FileExists
END /*FUNCTION*/

Then call the function from the query:

SELECT ...
FROM dbo.employees e
CROSS APPLY (
    SELECT 'C:\Portal\Images\EmployeePhotos\' + EmployeeCode + '.jpg' AS emp_picture_filename
) AS assign_alias_names
WHERE dbo.CheckFileExists(emp_picture_filename) = 0
1 Like
4

Thanks for the input, friends. I actually came up with a solution but it gave me a severe headache. lol

here's what I came up with: (Hope it helps others too)

DECLARE @empid VARCHAR(50) -- database name  
DECLARE @LastName VARCHAR(50) -- database name 
DECLARE @FirstName VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  

SET @path = 'C:\Portal\Images\EmployeePhotos\'  


DECLARE db_cursor CURSOR FOR  
SELECT EmployeeCode, LastName, FirstName
FROM UserProfile   

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @empid, @lastname, @firstname

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @empid + '.jpg'  
       --print @filename

	   if((select dbo.fc_FileExists(@fileName)) = 0)
	   print @empid + ' - ' + @lastname + ', ' + @firstname; 




       FETCH NEXT FROM db_cursor INTO @empid, @lastname, @firstname
   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor