1

Currently, I have a stored procedure that runs many different queries. One query in particular will perform a bulk insert of a CSV file stored on my computer, when a file is available, this bulk insert works great. Unfortunately, I don't always have a file for this query to retrieve, resulting in my stored procedure stopping and throwing an error when that file doesn't exist. What is a good way to handle this situation so that the stored procedure continues to execute fully, even if there isn't a file for the bulk insert query to retrieve?

I tried playing around with a try catch block, but it still gave me the same issues when I tried running the procedure. With that being said, it could have simply been because I didn't implement the try catch block correctly. I'm open to trying other ways besides try catch blocks, that was just the one I've seen used the most.

Jcmoney1010
  • 912
  • 7
  • 18
  • 41
  • TRY/CATCH works but that isn't really handling an error. That is more like suppressing an error. Why not check for the existence of the file and only try to process it when one exists? – Sean Lange Sep 08 '15 at 19:41
  • 1
    What did your catch block look like? `try catch` blocks are good for unpredictable errors - as this is predictable it might be better to use an `if` statement. See system stored procedure: dbo.xp_fileexist – PeteGO Sep 08 '15 at 19:41
  • @SeanLange I thought about that originally, but was having a hard time trying to figure out a way to check for the existence of the file. – Jcmoney1010 Sep 08 '15 at 19:46
  • 1
    Do you try http://stackoverflow.com/questions/11740000/check-for-file-exists-or-not-in-sql-server? – Lukasz Szozda Sep 08 '15 at 19:47

1 Answers1

2

You should be able to do a try catch like this:

BEGIN TRY
    -- Code that might error here
END TRY
BEGIN CATCH
    -- Handle the error here
END CATCH

But in your case your error is predictable, and you could check for the files existence and then use an if statement to only do the work if it exists.

Here's how to check for the existence of a file.

Replace @path with your file path. Eg: N'C:\Temp\Test.csv'

DECLARE @result INT
EXEC master.dbo.xp_fileexist @path, @result OUTPUT

IF (@result = 1)
BEGIN
    -- Do your bulk insert here.
END
PeteGO
  • 5,597
  • 3
  • 39
  • 70
  • The existence checking snippet worked great! I do have a question regarding the `Try..Catch`. If used in a stored procedure, could you start the try at the beginning of the stored procedure, and then end it at the ending of the procedure, or does this need to be performed on each query? – Jcmoney1010 Sep 08 '15 at 20:02
  • You could do it that way - assuming you can get what you need for the handling of the error from the `ERROR_MESSAGE()`, `ERROR_LINE()`, etc. – PeteGO Sep 10 '15 at 12:54