Stored procedure to handle multiple database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored procedure to handle multiple database

Author

Topic

gamaz2
Starting Member

31 Posts

Posted - 2008-07-21 : 12:59:46

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 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 varchar(50),
@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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 13:50:47

The error is obvious. you have a field which is either text,ntext or image type and as suggested by message you cant compare or sort them without using IS NULL or LIKE. If you're sure of their length you might try casting them to varchar/nvarchar

gamaz2
Starting Member

31 Posts

Posted - 2008-07-21 : 15:06:19

Thanks for your observation visakh16. I could not explain my problem properly. Let me try once more. I want to pass a parameter site(code). Based on that the database will be used. I have corrected the script for the table. Here is the corrected script:
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
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 (that corresponds to the site code)

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-21 : 15:16:17

You can't use the 'USE DbName' in your stored procedure, nor will it work with dynamic SQL like you've posted. You'll need to use the three-part naming convention for the objects instead.

DatabaseName.ObjectOwner.ObjectName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gamaz2
Starting Member

31 Posts

Posted - 2008-07-21 : 15:23:13

Thanks Tara for your input. Are you suggesting that in sql there is no solution for grabbing the database name dynamically based on a paramter (in the present case site)to process information corresponding to that database. Is there any alternative to use statement here? The one you suggested uses static database name. However my requirement is to use the database dynamically based on the site paramter passed. Any further thoughts. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-21 : 15:26:34

You should use static SQL for performance reasons. What you are attempting to do is bad for security and performance reasons. Do not do this dynamically just to save yourself some typing.

If you decide to go against the best practice, then I've shown you how to do it dynamically. You need to use the three-part naming convention in your stored procedure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gamaz2
Starting Member

31 Posts

Posted - 2008-07-21 : 16:04:06

OK Tara,

I have modified my code quite a bit and am trying to use the three part naming convention here. Here is my new procedure:

alter procedure customer_process_now

@custid varchar(50),
@site varchar(10)

as

declare @sitedbname varchar(25)
set @sitedbname = 'ts2' + @site + '_app'

select custid, sales_ytd, sales_lstyr
from @sitedbname.dbo.tblcustomer
where
custid = @custid

With the above change I am getting the following error:
Server: Msg 170, Level 15, State 1, Procedure customer_process_now, Line 12
Line 12: Incorrect syntax near '.'.

Any further input! Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-21 : 16:09:49

You'll need to use dynamic SQL if you are going to dynamically change the database name. Put the query into a variable and use @sitedbname + '.dbo.tblcustomer'.

But this is bad practice.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gamaz2
Starting Member

31 Posts

Posted - 2008-07-21 : 16:15:36

Thanks again Tara.

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2010-06-15 : 23:51:50

quote:
Originally posted by tkizer

You can't use the 'USE DbName' in your stored procedure, nor will it work with dynamic SQL like you've posted. You'll need to use the three-part naming convention for the objects instead.

DatabaseName.ObjectOwner.ObjectName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

3 part name convention doesn't work in my case. I want to create the same store in 20 different databases. Do you know any good approach?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-16 : 00:53:30

basicconfiguration, please open a new topic.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources