-
December 20th, 2012, 09:56 AM
#1
Stored Procedure 'Search through 2 different servers'
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|