Expand|Select|Wrap|Line Numbers
- Select [DatabaseName].[Owner].[TableName] .[field]
Test database ‘EOMMarch2010Prod’ & ‘EOMMarch2010Log’
Live database ‘Production’ & ‘Logistics’
This stored procedure(shown below) is effectively “hardwired” to the test database, which means I have to change the database name once I have finished testing and want to deploy it on to the live database.
I ‘ve tried changing
Expand|Select|Wrap|Line Numbers
- (SELECT TOP 100 PERCENT dbo.TraceSubAssy.PARENTJOBNUMBER AS Expr2
- FROM EOMMarch2010Log.dbo.[vw Stock Tracability Summary by Lot]
- (SELECT TOP 100 PERCENT dbo.TraceSubAssy.PARENTJOBNUMBER AS Expr2
- FROM @DatabaseName.dbo.[vw Stock Tracability Summary by Lot]
The full script is below:-
Expand|Select|Wrap|Line Numbers
- USE [EOMMarch2010Prod]
- GO
- /****** Object: StoredProcedure [dbo].[spPurchasesQueueFetch] Script Date: 05/20/2010 14:30:47 ******/
- SET ANSI_NULLS ON
- SET QUOTED_IDENTIFIER ON
- GO
- -- =============================================
- -- Author: Angela Andrews
- -- Create date: 20 May 20
- -- Description: used in recall Database
- -- =============================================
- Create PROCEDURE [dbo].[spEndCustomerSummaryQueue] @from_date datetime , @To_Date datetime, @Kitnumber NVARCHAR(20), @BatchLotNbr int, @DatabaseName varchar(50) , @Partnumber char(35) AS
- begin
- SET NOCOUNT ON;
- select ParentJobNumber, SUM(QTYDESP) AS [SumQtyDesp], NavSellToCustomerNbr, Company_Ref, OriginalJobNumber from dbo.TraceAssyNo
- WHERE (NavSellToCustomerNbr <> '') AND (SuperceededByRMA = '') AND (Scrapped = 0 )
- GROUP BY ParentJobNumber, NavSellToCustomerNbr, Company_Ref, OriginalJobNumber
- HAVING (ParentJobNumber IN
- (SELECT TOP 100 PERCENT dbo.TraceSubAssy.PARENTJOBNUMBER AS Expr2
- FROM EOMMarch2010Log.dbo.[vw Stock Tracability Summary by Lot] INNER JOIN
- dbo.traceKitNo ON EOMMarch2010Log.dbo.[vw Stock Tracability Summary by Lot].RELATEDKIT = dbo.traceKitNo.KITNUMBER INNER JOIN
- dbo.TraceSubAssy ON dbo.traceKitNo.SUBJOBNUMBER = dbo.TraceSubAssy.SUBJOBNUMBER
- GROUP BY EOMMarch2010Log.dbo.[vw Stock Tracability Summary by Lot].PARTNUMBER,
- EOMMarch2010Log.dbo.[vw Stock Tracability Summary by Lot].SUPPLIERLOTNBR, dbo.TraceSubAssy.PARENTJOBNUMBER
- HAVING (EOMMarch2010Log.dbo.[vw Stock Tracability Summary by Lot].PARTNUMBER = @Partnumber) AND
- ( EOMMarch2010Log.dbo.[vw Stock Tracability Summary by Lot].SUPPLIERLOTNBR = @BatchLotNbr) ))
- end