prevent error when target database in restore mode (sql preparation)

Question

I have a stored procedure that runs nightly. It pulls some data from a linked server and inserts it into a table on the server where the sql agent job runs. Before the INSERT statement is run, the procedure checks if the database on the linked server is online (STATE = 0). If not the INSERT statement is not run.

IF EXISTS(
SELECT *
FROM OPENQUERY(_LINKEDSERVER,'
SELECT name, state FROM sys.databases
WHERE name = ''_DATABASENAME'' AND state = 0')
)
BEGIN
INSERT INTO _LOCALTABLE (A, B)
SELECT A, B FROM _LINKEDSERVER._DATABASENAME.dbo._REMOTETABLE
END

But the procedure gives an error (deferred prepare could not be completed) when the remote database is in restore mode. This is because the statement between BEGIN and END is evaluated before the whole script is run. Also when the IF evaluation is not true. And because _DATABASENAME is in restore mode this already gives an error.

As a workaround I placed the INSERT statement in an execute function:

EXECUTE('INSERT INTO _LOCALTABLE (A, B) 
SELECT A, B FROM _LINKEDSERVER._DATABASENAME.dbo._REMOTETABLE')

But is there another more elegant solution to prevent the evaluation of this statement before this part of the sql is used?

My scenario involves a linked server. Off course the same issue is when the database is on the same server.

I was hoping for some command I am not aware of yet, that prevents evaluation syntax inside an IF:

IF(Evaluation)
BEGIN
    PREPARE THIS PART ONLY IF Evaluation IS TRUE.
END

edit regarding answer:

I tested:

IF(EXISTS
(
SELECT *
FROM sys.master_files F WHERE F.name = 'Database'
AND state = 0
))
BEGIN
    SELECT * FROM Database.dbo.Table
END
ELSE
BEGIN
    SELECT 'ErrorMessage'
END

Which still generates this error: Msg 942, Level 14, State 4, Line 8 Database 'Database' cannot be opened because it is offline.

Answer 1

I don't think there's a way to conditionally prepare only part of a t-sql statement (at least not in the way you've asked about).

The underlying problem with your original query isn't that the remote database is sometimes offline, it's that the query optimizer can't create an execution plan when the remote database is offline. In that sense, the offline database is effectively like a syntax error, i.e. it's a condition that prevents a query plan from being created, so the whole thing fails before it ever gets a chance to execute.

The reason EXECUTE works for you is because it defers compilation of the query passed to it until run-time of the query that calls it, which means you now have potentially two query plans, one for your main query that checks to see if the remote db is available, and another that doesn't get created unless and until the EXECUTE statement is actually executed.

So when you think about it that way, using EXECUTE (or alternatively, sp_executesql) is not so much a workaround as it is one possible solution. It's just a mechanism for splitting your query into two separate execution plans.

With that in mind, you don't necessarily have to use dynamic SQL to solve your problem. You could use a second stored procedure to achieve the same result. For example:

-- create this sp (when the remote db is online, of course)
CREATE PROCEDURE usp_CopyRemoteData 
AS
BEGIN
  INSERT INTO _LOCALTABLE (A, B)
  SELECT A, B FROM _LINKEDSERVER._DATABASENAME.dbo._REMOTETABLE;
END
GO

Then your original query looks like this:

IF EXISTS(
  SELECT *
  FROM OPENQUERY(_LINKEDSERVER,'
  SELECT name, state FROM sys.databases
  WHERE name = ''_DATABASENAME'' AND state = 0')
  )
BEGIN
  exec usp_CopyRemoteData;
END

Another solution would be to not even bother checking to see if the remote database is available, just try to run the INSERT INTO _LOCALTABLE statement and ignore the error if it fails. I'm being a bit facetious, here, but unless there's an ELSE for your IF EXISTS, i.e. unless you do something different when the remote db is offline, you're basically just suppressing (or ignoring) the error anyway. The functional result is the same in that no data gets copied to the local table.

You could do that in t-sql with a try/catch, like so:

BEGIN TRY
  /* Same definition for this sp as above. */
  exec usp_CopyRemoteData;

  /* You need the sp; this won't work:
  INSERT INTO _LOCALTABLE (A, B)
  SELECT A, B FROM _LINKEDSERVER._DATABASENAME.dbo._REMOTETABLE
  */
END TRY
BEGIN CATCH
  /* Do nothing, i.e. suppress the error. 
    Or do something different?
  */
END CATCH

To be fair, this would suppress all errors raised by the sp, not just ones caused by the remote database being offline. And you still have the same root issue as your original query, and would need a stored proc or dynamic SQL to properly trap the error in question. BOL has a pretty good example of this; see the "Errors Unaffected by a TRY…CATCH Construct" section of this page for details: http://technet.microsoft.com/en-us/library/ms175976(v=sql.105).aspx

The bottom line is that you need to split your original query into separate batches, and there are lots of ways to do that. The best solution depends on your specific environment and requirements, but if your actual query is as straightforward as the one presented in this question then your original workaround is probably a good solution.