This is my script - it simply looks for the image file associated to a person record.

Now the error will display if there is NO .jpg evident when the query runs.

Msg 4860, Level 16, State 1, Line 1 Cannot bulk load. The file "C:\Dev\ClientServices\Defence\RAN\Shore\Config\Photos\002054.2009469432270600.001.jpg" does not exist.

Is there a way to write this query to 'IF not exists then set id_number = '002054.2009469432270427.001' - so it wil always display this photo for any records without a picture.

ALTER
procedure [dbo].[as_ngn_sp_REP_PH108_photo] (@PMKEYS nvarchar(50)) 
AS
---exec [as_ngn_sp_REP_PH108_photo] '8550733'
SET 
NOCOUNT ON

DECLARE @PATH AS NVARCHAR(255)
DECLARE @ID_NUMBER NVARCHAR(27)
DECLARE @SQL AS NVARCHAR(MAX)

EXEC DB_GET_DB_SETTING'STAFF PICTURE FILE LOCATION', 0, @PATH OUTPUT 

IF RIGHT(@PATH,1) <> '\'

    SET @PATH = @PATH + '\' 

SELECT @ID_NUMBER = ID_NUMBER FROM aView_person WHERE EXTRA_CODE_1 = @PMKEYS

SET @PATH = @PATH + @ID_NUMBER + '.jpg'

SET @SQL = 'SELECT ''Picture1'' [Picture], BulkColumn FROM OPENROWSET(Bulk ''' + REPLACE(@PATH,'''','''''') + ''', SINGLE_BLOB) AS RAN'

EXEC SP_EXECUTESQL @SQL