473,327 Members | 2,074 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Handling multiple database with stored procedure

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
4 13103
ck9663
2,878 Expert 2GB
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
gamaz
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
2,878 Expert 2GB
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
gamaz
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

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

Similar topics

3
by: DarthMacgyver | last post by:
Hello, I recently wrote a survey application. Each question is very similar. The first questions gives me a problem when there are multiple people taking the survey (The Database connection...
1
by: Jon LaRosa | last post by:
Hi all - I have a web application and I want to be able to do some basic error handling. For example, here is one error I would like to catch and display in a useful way for the user: ...
2
by: xAvailx | last post by:
I have a requirement that requires detection of rows deleted/updated by other processes. My business objects call stored procedures to create, read, update, delete data in a SQL Server 2000 data...
2
by: Paul Hale | last post by:
Hi all, being new to c# I'm trying to find the best way of passing multiple records to insert into a sql database via a stored procedure. I'm using visual studio 2005 RC SQL server 2005 and C# of...
1
by: Andrew | last post by:
Hey all, I am very new to ASP.Net (and .Net in general), but that isn't stopping the boss from wanting to begin new projects in it. This latest project has me kinda stumped and after a couple...
6
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing...
4
by: James Radke | last post by:
Hello, I am looking for guidance on best practices to incorporate effective and complete error handling in an application written in VB.NET. If I have the following function in a class module...
5
by: Jurgen Defurne | last post by:
I am currently designing an application which should be accessible from different interfaces. For this I like to be using stored procedures to process the contents of form submissions and dialog...
43
by: bonneylake | last post by:
Hey Everyone, Well this is my first time asking a question on here so please forgive me if i post my question in the wrong section. What i am trying to do is upload multiple files like gmail...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.