How to make Database name a variable in a stored procedure?

[AngelaA]

The syntax to access a table is

Select   [DatabaseName].[Owner].[TableName] .[field] 

How do I make the DatabaseName a variable – ideally to be passed as a parameter into the stored procedure? The reason being my test database uses a different set of tables to the live database. Eg

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

(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]

But it doesnt like it. Any ideas?
The full script is below:-

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
 
 

[Delerna]

You would need to use dynamic sql for that. Check it in the help files for SQL server.

I would suggest that you don't pass the table name as a parameter though. You open yourself to SQL injection attacks.
It would become possible for someone to pass something like "DROP Table tableName" into your stored proc and then dynamic sql might execute it.


Better to pass a parameter for an
IF BEGIN END ELSE BEGIN END
test
You wouldn't need to use dynamic SQL at all then.

you could then say in the stored proc

IF @Param='TEST'
BEGIN
    --SQL to access the test database
END ELSE BEGIN
    --SQL to access the prod database
END