Handling multiple database with stored procedure

[gamaz]

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

[ck9663]

You can do an IF inside your SP

IF @parameterdbname = this
   select and do this
IF @parameterdbname = this
   select and do this
IF @parameterdbname = this
   select and do this
 

or

do a

EXEC('select * from ' + @parameterdbname + '.dbo.tblCustomer where condition = true')
 

Good luck

-- CK

[gamaz]

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

IF @parameterdbname = this
   select and do this
IF @parameterdbname = this
   select and do this
IF @parameterdbname = this
   select and do this
 

or

do a

EXEC('select * from ' + @parameterdbname + '.dbo.tblCustomer where condition = true')
 

Good luck

-- CK

[ck9663]

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

[gamaz]

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