I am having difficulty writing the SQl for my stored procedure. The stored procedure consists of "Dynamic stored procedure to allow barcode lookup across all lines and databases. Accepts only a barcode, then searches a lookup table for all barcode types that match, then requests information from the appropriate databases". I need to add to the stored procedure " Search through ServerA for the information and if not found, Search through ServerB for the information". Here is the Code that I have already created.

USE [Database_1]
GO
/****** Object: StoredProcedure [dbo].[spBarcodeLookup] Script Date: 12/17/2012 08:06:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[spBarcodeLookup]
@Barcode VARCHAR(30)
AS
DECLARE @DatabaseName VARCHAR(50),
@BarcodeCode VARCHAR(10),
@PrefixSuffix VARCHAR(10),
@Return INT,
@Query VARCHAR(500)

BEGIN
SET NOCOUNT ON;

DECLARE DatabaseCursor CURSOR FOR
SELECT fdBarcodeCode, fdPrefixSuffix, fdDatabase
FROM [Database_1].dbo.tbBarcodeLookup

CREATE TABLE #tmpBarcodeLookup
(Barcode VARCHAR(30),
ContainerSN VARCHAR(10),
ShortNumber VARCHAR(10),
ClockNumber VARCHAR(10),
EnterDate DATETIME)

OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO
@BarcodeCode, @PrefixSuffix, @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
IF @PrefixSuffix = 'Prefix'
BEGIN
IF LEFT(@Barcode, LEN(@BarcodeCode)) = @BarcodeCode
BEGIN
SET @Query = '[' + @DatabaseName + '].dbo.spBarcodeLookup ''' + @Barcode + ''''
--SELECT @Query

INSERT INTO #tmpBarcodeLookup
EXEC (@Query)
END
END
ELSE
BEGIN
IF RIGHT(@Barcode, LEN(@BarcodeCode)) = @BarcodeCode
BEGIN
SET @Query = '[' + @DatabaseName + '].dbo.spBarcodeLookup ''' + @Barcode + ''''
--SELECT @Query

INSERT INTO #tmpBarcodeLookup
EXEC (@Query)
END

END

FETCH NEXT FROM DatabaseCursor INTO
@BarcodeCode, @PrefixSuffix, @DatabaseName
END


CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

SELECT Tmp.Barcode,
Tmp.ContainerSN,
Tmp.ShortNumber,
Tmp.ClockNumber,
Tmp.EnterDate,
Ship.fdShipper AS Shipper,
Ship.fdScannedTime AS ScannedDate,
Ship.fdShipDate AS ShipDate
FROM #tmpBarcodeLookup Tmp LEFT OUTER JOIN
[AS400].dbo.tbShipperInfo Ship
ON Ship.fdSerial LIKE '%Tmp.ContainerSN%'

DROP TABLE #tmpBarcodeLookup

END