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 ‘EOMMarch2010Pr od’ & ‘EOMMarch2010Lo g’
Live database ‘Productio n’ & ‘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
-
-
1 3775 Delerna 1,134
Recognized Expert Top Contributor
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
-
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Orlando Jin |
last post by:
How can I get database SID in stored procedure in Oracle?
|
by: Robin Tucker |
last post by:
Is it possible to execute a stored procedure in one database, which then
itself executes a stored procedure from another database? We have decide to
split our data into a tree structure (DB1) and data blobs (DB2) (we are
using MSDE and we have a 2gb limit with each DB so we've done it this way
for that reason). I would like to, say, execute a stored procedure in DB1,
passing in the data blob and other details, DB1 will create a tree node...
|
by: Bruce |
last post by:
I need to create a stored procedure in the master database that can
access info to dynamically create a view in another database. It
doesn't seem like it should be very hard, but I can't get it to work.
Here's an example of what I want to do.
CREATE PROCEDURE create_view @dbname sysname
AS
BEGIN
DECLARE @query varchar(1000)
SELECT @query = 'use ' + @dbname + ' go CREATE VIEW ........'
|
by: Michael Spiegel |
last post by:
Hi,
I have a stored procedure that needs to know the name of the table
from which it is called. If you are familiar with a "this" pointer in
Java or C++, that's very similar to what I need. I know I can use
db_name() to retrieve the database name, but how do retrieve the table
name?
Thanks,
--Michael
|
by: Justin |
last post by:
I am trying to do a simple update of a database using a stored procedure. I
have get the following error when executing the code:
"Procedure or function UpdateDetails has too many arguments specified."
Here is my SP:
ALTER PROCEDURE dbo.UpdateDetails
@KeyID int
| |
by: jojo41300000 |
last post by:
Hi,
I am trying to pass the table name to stored procedure and then use the passing table name in select, update or delete query statement.
When I tried to do that, I got the error such as '@tablename must be declared'.
The example syntax would be like below.
CREATE PROCEDURE procedure_name
@tablename varchar(50)
AS
|
by: ishakteyran |
last post by:
hi all;
i am using a db2 database on RAD platform and need to generate tables (names of them will be determined by users.) so i thought i can do this with a stored procedure which takes the table name as input. but i live difficulty with usimg the table name ..
create procdure proc1 (IN inName VARCHAR(30))
BEGIN
Create TAble inName(this is table name variable) (and the rest of the table definition..)
END
the code snippet...
|
by: ssouhrada |
last post by:
I am trying to create a stored procedure that runs a smaller query first to determine the database to run the second query on. Below is a sample query that is similar to what I would be doing. I'm not interested in creating new Stored Procedures as their are hundreds that are like this so I don't want to write new statements like...
EXEC( "Select * from " + @TableName + " where " + CAST(@Param1 as varchar) +
" = " + @Param2
GO
as that...
|
by: barmatt80 |
last post by:
I am in need of calling a DB2 stored procedure from MS SQL server 2005. If this is all possible.
The ultimate goal is to have a user open the vb.net program, enter the employee ssn, and it will return some employee information. The ssn would be passed from the program to the sql stored procedure, which inturn would pass along the ssn to the db2 stored procedure, which would return Employee first name, last name, annual, sick, other and...
|
by: qwedster |
last post by:
Folk!
How to programattically check if null value exists in database table (using stored procedure)?
I know it's possble in the Query Analyzer (see last SQL query batch statements)?
But how can I pass null value as parameter to the database stored procedure programattically using C#?
Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |