By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,310 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,310 IT Pros & Developers. It's quick & easy.

Handling multiple database with stored procedure

P: 4
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] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sales_ytd] [money] NULL ,
[sales_lstyr] [money] NULL
) ON [PRIMARY] TEXTIMAGE_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 database name, custid, sales_ytd, sales_lstyr and a sql paramter to process the database name.

When I am creating the stored procedure I am getting the following error:

Server: Msg 306, Level 16, State 1, Procedure process_customer, Line 13
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I am not sure why I am not getting this error. Any help to resolve this is highly appreciated. Thanks.



STORED PROCEDURE CODE:


create procedure process_customer

--DECLARE
@dbname varchar(255),
@custid int,
@sales_ytd money,
@sales_lstyr money,
@site varchar(10),
@SQL varchar(50)
as
set @dbname = 'ts2_' + @site + '_app'
set @SQL = 'use ' + @dbname
select custid, sales_ytd, sales_lstyr
from tblcustomer
where
custid = @custid
and sales_ytd = @sales_ytd
and sales_lstyr = @sales_lstyr
Jul 21 '08 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
You can do an IF inside your SP

Expand|Select|Wrap|Line Numbers
  1. IF @parameterdbname = this
  2.    select and do this
  3. IF @parameterdbname = this
  4.    select and do this
  5. IF @parameterdbname = this
  6.    select and do this
  7.  
or

do a

Expand|Select|Wrap|Line Numbers
  1. EXEC('select * from ' + @parameterdbname + '.dbo.tblCustomer where condition = true')
  2.  
Good luck

-- CK
Jul 21 '08 #2

P: 4
Thanks for your help CK. I appreciate it very much. Actually I think I could not explain the problem I am having properly. I need to use the database based on the parameter passed to the site. The correct procedure logic will be as follows:
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.
So, I need to use the site parameter in order to determine which database to use. Any further thoughts? Sorry for the confusion. Regards.

You can do an IF inside your SP

Expand|Select|Wrap|Line Numbers
  1. IF @parameterdbname = this
  2.    select and do this
  3. IF @parameterdbname = this
  4.    select and do this
  5. IF @parameterdbname = this
  6.    select and do this
  7.  
or

do a

Expand|Select|Wrap|Line Numbers
  1. EXEC('select * from ' + @parameterdbname + '.dbo.tblCustomer where condition = true')
  2.  
Good luck

-- CK
Jul 21 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Thanks for your help CK. I appreciate it very much. Actually I think I could not explain the problem I am having properly. I need to use the database based on the parameter passed to the site. The correct procedure logic will be as follows:
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.
So, I need to use the site parameter in order to determine which database to use. Any further thoughts? Sorry for the confusion. Regards.

Yes. On my pseudocode, your @site is my @parameterdbname. What I gave you is a pseudocode that you can use as pattern, not actual code.

-- CK
Jul 21 '08 #4

P: 4
Thanks for your help. I appreciate it.

Yes. On my pseudocode, your @site is my @parameterdbname. What I gave you is a pseudocode that you can use as pattern, not actual code.

-- CK
Jul 21 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.