How to know which users have a photo in a folder via OPENROWSET?

Question

I have a system where photos are stored in briefcase and not in the database. To save the photo file, the system uses the following standardization:

Username: Otávio Augusto | Primary Key: 12345

Photo file name: OTAVIO_AUGUSTO_12345.jpg

Imagine the way to the pictures is: C: System images, in this way, it would be possible to find the photo of this user on the way: "C: System images OTAVIO_AUGUSTO_12345.jpg".

If my intention was to present exactly this photo in a report, in a non-dynamic way, I would use the following instruction:

Select
    BulkColumn
    FROM OPENROWSET(BULK N'C:\SophiA\imagens\OTAVIO_AUGUSTO_13965.jpg' , SINGLE_BLOB) Load

Where would return me the binary for photo presentation.

However, what I need to know is in a list of users, which have photo and which do not, for example:

Select

    PK_USUARIO,
    Case 
        When BulkColumn is not null then 'Possuí Foto'
        Else 'Não Possuí Foto'
    End AS VERIFICA_FOTO

    FROM OPENROWSET(BULK N'C:\SophiA\imagens\ + NOME_USUARIO + '_' 
                                              + PK_USUARIO 
                                              +'.jpg' , SINGLE_BLOB) Load
Where 

PK_USUARIO In (12345,22222,33333)

And the desired return would be something like:

PK_USUARIO  VERIFICA_FOTO
12345       Possuí Foto
22222       Não Possuí Foto
33333       Não Possuí Foto

The problem is that if there is no photo in the folder, instead of "Null", SQL returns an error.

(Mensagem 4860, Nível 16, Estado 1, Linha 1 Não é possível carregar em massa. O arquivo "C:\Sistema\imagens\OTAVIO_AUGUSTO_13965.jpg" não existe ou você não tem direitos de acesso ao arquivo.)

How to work this query in order to treat this error and return which users have photo and which ones do not? If there is at least one user without a photo, then the error will be shown. It is possible to use TRY CATCH?

Thank you for your attention.

Answer 1

The problem was solved through the trial:

CREATE PROCEDURE FOTO_USUARIO  
                   @Codigo integer,  
                   @TipoPessoa integer,  
                   @FOTO VARBINARY(max) OUTPUT  
                   AS    
                   begin  
                           DECLARE @FILE VARCHAR(100);  
                           DECLARE @nome_usuario VARCHAR(100);  
                           DECLARE @CAMINHO NVARCHAR(MAX);  
                           DECLARE @COMANDO NVARCHAR(MAX);  
                           DECLARE @Parametro NVARCHAR(max);  
                           DECLARE @PATH VARCHAR(MAX);  
                           DECLARE @CODEXT VARCHAR(17);  
                           DECLARE @TIPO_GRAVACAO_FOTO INTEGER;  
                       SELECT @TIPO_GRAVACAO_FOTO = NOMEARQUIVOWEBCAM,  
                               @PATH = COALESCE(PATH_FOTO_ALT, PATH_FOTO)  
                           FROM CONFIG WITH(NOLOCK)   
                           SET @Parametro = N'@fotoParametro varbinary(max) output';  
                       IF (@TipoPessoa = 1)  
                           BEGIN  
                               SELECT @FILE = NOME,  
                                       @CODIGO = CODIGO,  
                                       @CODEXT = CODEXT,  
                                       @nome_usuario = NOME   
                                   FROM FISICA WITH(NOLOCK)   
                               WHERE CODIGO = @Codigo  
                           IF (@TIPO_GRAVACAO_FOTO = 1)  
                           BEGIN  
                               SET @FILE = @CODEXT;  
                           END;             
                               SET @FILE = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(  
                                               REPLACE(REPLACE(REPLACE(  
                                               REPLACE(REPLACE(REPLACE(  
                                               REPLACE(@FILE, ' ', '_'),'\',''),'/',''),':',''),  
                                               '*',''),'?',''),'>',''),'<',''),'|',''),'"',''),'.',''),'''','') + '_' +   
                                               CAST(@CODIGO AS VARCHAR) + '.JPG';         
                       END  
                       ELSE IF (@TipoPessoa = 2)  
                       BEGIN  
                               SELECT @FILE = PAI_NOME,  
                                       @CODIGO = FA.CODIGO   
                                   FROM FAMILIAS FA WITH(NOLOCK)  
                               INNER JOIN DADOSPF D WITH(NOLOCK)  
                                   ON D.FAMILIA = FA.CODIGO             
                               WHERE D.FAMILIA = @Codigo  
                               SET @FILE = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(  
                                               REPLACE(REPLACE(REPLACE(  
                                               REPLACE(REPLACE(REPLACE(  
                                               REPLACE(@FILE, ' ', '_'),'\',''),'/',''),':',''),  
                                               '*',''),'?',''),'>',''),'<',''),'|',''),'"',''),'.',''),'''','') + '_' +   
                                               CAST(@CODIGO AS VARCHAR) + '_pai.JPG';           
                       END  
                       ELSE IF (@TipoPessoa = 3)  
                       begin  
                               SELECT @FILE = MAE_NOME,  
                                       @CODIGO = FA.CODIGO    
                                   FROM FAMILIAS FA WITH(NOLOCK)  
                               INNER JOIN DADOSPF D WITH(NOLOCK)  
                                   ON D.FAMILIA = FA.CODIGO             
                               WHERE D.FAMILIA = @Codigo  
                               SET @FILE = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(  
                                               REPLACE(REPLACE(REPLACE(  
                                               REPLACE(REPLACE(REPLACE(  
                                               REPLACE(@FILE, ' ', '_'),'\',''),'/',''),':',''),  
                               '*',''),'?',''),'>',''),'<',''),'|',''),'"',''),'.',''),'''','') + '_' +   
                                               CAST(@CODIGO AS VARCHAR) + '_mae.JPG';   
                       END  
                                           SET @FILE = REPLACE(REPLACE(REPLACE(@FILE, 'É','E'),'È','E'),'Ê','E');  
                                       SET @FILE = REPLACE(REPLACE(REPLACE(@FILE, 'Í','I'),'Ì','I'),'Î','I');  
                                       SET @FILE = REPLACE(REPLACE(REPLACE(REPLACE(@FILE, 'Á','A'),'À','A'),'Â','A'),'Ã','A');  
                                       SET @FILE = REPLACE(REPLACE(REPLACE(REPLACE(@FILE, 'Ó','O'),'Ò','O'),'Ô','O'),'Õ','O');  
                                       SET @FILE = REPLACE(REPLACE(REPLACE(@FILE, 'Ú','U'),'Ù','U'),'Û','U');  
                                       SET @FILE = REPLACE(REPLACE(@FILE, 'Ý','Y'),'`Y','Y');                                   
                                           SET @FILE = REPLACE(@FILE, 'Ç','C');  
                                           set @CAMINHO = @PATH + '\' + @FILE;  
                         print @CAMINHO;  
                           SET @COMANDO = N'SELECT @fotoParametro = BulkColumn FROM OPENROWSET(BULK N' + '''' + @CAMINHO + '''' + ' , SINGLE_BLOB) Load'  
                           BEGIN TRY  
                               EXEC sp_executesql @COMANDO, @Parametro, @fotoParametro = @foto OUTPUT;  
                           END TRY  
                           BEGIN CATCH  
                               set @foto = NULL  
                           END CATCH  
                           if ((@foto is null) and (@TipoPessoa = 1))  
                           begin  
                               if (@TIPO_GRAVACAO_FOTO = 1)  
                               begin  
                               SET @FILE = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(  
                                               REPLACE(REPLACE(REPLACE(  
                                               REPLACE(REPLACE(REPLACE(  
                                               REPLACE(@NOME_USUARIO, ' ', '_'),'\',''),'/',''),':',''),  
                                               '*',''),'?',''),'>',''),'<',''),'|',''),'"',''),'.',''),'''','') + '_' +   
                                               CAST(@CODIGO AS VARCHAR) + '.JPG';   
                               end  
                               else  
                               begin  
                               SET @FILE = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(  
                                               REPLACE(REPLACE(REPLACE(  
                                               REPLACE(REPLACE(REPLACE(  
                                               REPLACE(@CODEXT, ' ', '_'),'\',''),'/',''),':',''),  
                                               '*',''),'?',''),'>',''),'<',''),'|',''),'"',''),'.',''),'''','') + '_' +   
                                               CAST(@CODIGO AS VARCHAR) + '.JPG';             
                               end  
                                               SET @FILE = REPLACE(REPLACE(REPLACE(@FILE, 'É','E'),'È','E'),'Ê','E');  
                                       SET @FILE = REPLACE(REPLACE(REPLACE(@FILE, 'Í','I'),'Ì','I'),'Î','I');  
                                       SET @FILE = REPLACE(REPLACE(REPLACE(REPLACE(@FILE, 'Á','A'),'À','A'),'Â','A'),'Ã','A');  
                                       SET @FILE = REPLACE(REPLACE(REPLACE(REPLACE(@FILE, 'Ó','O'),'Ò','O'),'Ô','O'),'Õ','O');  
                                       SET @FILE = REPLACE(REPLACE(REPLACE(@FILE, 'Ú','U'),'Ù','U'),'Û','U');  
                                       SET @FILE = REPLACE(REPLACE(@FILE, 'Ý','Y'),'`Y','Y');        
                                       SET @FILE = REPLACE(@FILE, 'Ç','C');         
                               set @CAMINHO = @PATH + '\' + @FILE;  
                               SET @COMANDO = N'SELECT @fotoParametro = BulkColumn FROM OPENROWSET(BULK N' + '''' + @CAMINHO + '''' + ' , SINGLE_BLOB) Load'  
                               BEGIN TRY  
                                       EXEC sp_executesql @COMANDO, @Parametro, @fotoParametro = @foto OUTPUT;  
                               END TRY  
                               BEGIN CATCH  
                                       set @foto = NULL  
                               END CATCH  
                           end  
                   end

I could not return in batch, because it is a Procedure. But I could return if the Registration has photo or not through the system report, on screen.