473,386 Members | 1,962 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,386 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 13109
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:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

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.