July 21, 2008 at 12:17 pm
Hi,
I am trying to work on a stored procedure that will work with multiple database. I have a prototype of multiple databases. Those are named as the following: ts2_aldkm_app, ts2_aldkp_app, ts2_aldkt_app. The middle part of the database name corresponds to the site name e.g aldkm corresponds to site aldkm etc. Each database has one table tblCustomer which is scripted as follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblcustomer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblcustomer]
GO
CREATE TABLE [dbo].[tblcustomer] (
[RecKey] [int] NOT NULL ,
[CustID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sales_ytd] [money] NULL ,
[sales_lstyr] [money] NULL
) ON [PRIMARY]
GO
Now I need to create a stored procedure that should work with any of the three ( or more ) database if proper parameters are passed. I need to pass the custid, sales_ytd, sales_lstyr and site to process the customer table information corresponding to a particular database
STORED PROCEDURE CODE:
create procedure process_customer
@custid varchar(50),
@sales_ytd money,
@sales_lstyr money,
@site varchar(10)
as
use 'ts2_' + @site + '_app'
select custid, sales_ytd, sales_lstyr
from tblcustomer
where
custid = @custid
and sales_ytd = @sales_ytd
and sales_lstyr = @sales_lstyr
With the above I am getting the following error:
Server: Msg 154, Level 15, State 1, Procedure process_customer, Line 13
a USE database statement is not allowed in a procedure or trigger.
The use statement is screwing things up. Is there any way one can use the above procedure dynamically i.e. based on site value the database will be 'used' by the procedure.
July 21, 2008 at 3:22 pm
If these data bases are on the same machine you might try something like this:
declare dnameStr varchar(30)
set dnameStr = 'ts2_' + @site + '_app'
select custid, sales_ytd, sales_lstyr
from dnameStr.dbo.tblcustomer
where
custid = @custid
and sales_ytd = @sales_ytd
and sales_lstyr = @sales_lstyr
If these data bases are on different machines, you could use distributed transaction processing protocol in a similar manner.
Hope this helps.
July 21, 2008 at 3:31 pm
tbeadle (7/21/2008)
If these data bases are on the same machine you might try something like this:declare dnameStr varchar(30)
set dnameStr = 'ts2_' + @site + '_app'
select custid, sales_ytd, sales_lstyr
from dnameStr.dbo.tblcustomer
where
custid = @custid
and sales_ytd = @sales_ytd
and sales_lstyr = @sales_lstyr
If these data bases are on different machines, you could use distributed transaction processing protocol in a similar manner.
Hope this helps.
Won't work. You need to use dynamic sql if you need to run the same procedure against a table in multiple databases on the same server.
😎
July 21, 2008 at 3:45 pm
Here is a sample, but be forwarned. the code below is not protected from a SQL Injection Attack. There are ways to write this to protect you, but I wanted to give you an idea of how this can be done using dynamic sql. You can do a search of SSC to find out more, plus I'm sure some of the other guru's out there may take my code and show you the necessary mods required.
create procedure dbo.process_customer
@custid varchar(50),
@sales_ytd money,
@sales_lstyr money,
@site varchar(10)
as
begin
---- NOTE ---- This code does not protect against a SQL Injection attack
---- NOTE ---- This code needs additional work to protect you
declare @SQLCmd nvarchar(4000) -- max size for SQL Server 2000
set @SQLCmd = N'use ts2_' + @site + N'_app' + char(10) + char(13)
set @SQLCmd = @SQLCmd + N'select custid, sales_ytd, sales_lstyr from dbo.tblcustomer where '
set @SQLCmd = @SQLCmd + N'custid = ' + @custid + N' and sales_ytd = ' + cast(@sales_ytd as nvarchar(12))
set @SQLCmd = @SQLCmd + N' and sales_lstyr = ' + cast(@sales_lstyr as nvarchar(12))
exec (@SQLCmd)
end
😎
July 21, 2008 at 4:00 pm
To do something like this you are going to have to use dynamic SQL. You might want to consider putting a procedure in each database instead and call the procedure in that database.
For more information on using dynamic sql, see the following article:
http://www.sommarskog.se/dynamic_sql.html[/sql]
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 21, 2008 at 4:11 pm
OK Lynn,
I have changed the stored procedure in accordance with a requirement change. With this the procedure changes to the following:
create procedure dbo.process_customer_modified
@custid varchar(50),
@site varchar(10)
as
begin
---- NOTE ---- This code does not protect against a SQL Injection attack
---- NOTE ---- This code needs additional work to protect you
declare @SQLCmd nvarchar(4000) -- max size for SQL Server 2000
set @SQLCmd = N'use ts2_' + @site + N'_app' + char(10) + char(13)
set @SQLCmd = @SQLCmd + N'select custid, sales_ytd, sales_lstyr from dbo.tblcustomer where '
set @SQLCmd = @SQLCmd + N'custid = ' + @custid
exec (@SQLCmd)
end
The procedure compiles fine. However when I am trying to execute the procedure I am getting error. Any thoughts:
process_customer_modified @sqlcmd, 'c000001', 'aldkm'
July 21, 2008 at 4:34 pm
gamaz (7/21/2008)
The procedure compiles fine. However when I am trying to execute the procedure I am getting error. Any thoughts:process_customer_modified @sqlcmd, 'c000001', 'aldkm'
The call to the procedure should be:
Execute process_customer_modified 'c000001', 'aldkm';
You can leave off the execute if it is the first statement in the batch. If not, you need it - so I always include it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 21, 2008 at 5:46 pm
You may create this procedure as system procedure in master database (that's where prefix "sp_" is required).
Then you may call it like:
EXEC TargetDB.dbo.sp_process_customer
or just call it from TargetDB
USE TargetDB
GO
EXEC dbo.sp_process_customer
and it will be executed in context of TargetDB.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply