While linked server is closed, exec the stored procedure will get an error

Question

I have a stored procedure for select data between many different sql server, and all the sql server have set the link server at the sql server where the stored procedure built in.

Here is my procedure:

Create Proc dbo.spGetData
@code as char(4)
AS
if (@code='aaaa')
  Select date From [ServerA].Stock.dbo.Syspara
else if (@code='bbbb')
  Select date From [ServerB].Stock.dbo.Syspara
else if (@code='cccc')
  Select date From [ServerC].Stock.dbo.Syspara
else if (@code='dddd')
  Select date From [ServerD].Stock.dbo.Syspara
GO

If the [ServerB] isn't alive or is closed, when I call:

exec dbo.spGetData 'dddd'

There will be an error, but if the all 4 server are alive, the query will return without error.

How can I do to avoid the problem?

Answer 1

Add TRY..CATCH error handling:

The following example shows how an object name resolution error generated by a SELECT statement is not caught by the TRY…CATCH construct, but is caught by the CATCH block when the same SELECT statement is executed inside a stored procedure.

BEGIN TRY
    -- Table does not exist; object name resolution
    -- error not caught.
    SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH

The error is not caught and control passes out of the TRY…CATCH construct to the next higher level.

Running the SELECT statement inside a stored procedure will cause the error to occur at a level lower than the TRY block. The error will be handled by the TRY…CATCH construct.

-- Verify that the stored procedure does not exist.
IF OBJECT_ID ( N'usp_ExampleProc', N'P' ) IS NOT NULL 
    DROP PROCEDURE usp_ExampleProc;
GO

-- Create a stored procedure that will cause an 
-- object resolution error.
CREATE PROCEDURE usp_ExampleProc
AS
    SELECT * FROM NonexistentTable;
GO

BEGIN TRY
    EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH;

Answer 2

If, like me, you still have to use SQL Server 2000, then you can't use try catch blocks.

I don't think it will help with the time out, but if you break your if statements out into individual statements and check @@ERROR after each one, that will get get you better control.