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
4 13103
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
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
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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:
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
| |