Re: Variable-named database in a proc
- From: "m" <m@xxx>
- Date: Fri, 11 Dec 2009 22:47:39 -0500
In my experience, dynamic SQL has been the best method. Back in they day, when commodity HW boasted 2GB HDDs and PI 133 MHz CPUs, I used a similar method for linked server queries across a set of sub-servers where the names were only known at run time based on the value of a partial logical key referencing a partition table - giving the query reasonable (sub-second m_tree like) performance for PK entity select & update across a multi-TB database. Fortunately such evils are mostly in the past!
one should realize that schema names are amongst the hardest parts of any system to change later and choose carefully in consequence. Sometimes one has no choice - then views are your friend - but where possible, choose wisely the first time and then don't need to change it.
"Rick" <rick0726@xxxxxxxxxxx> wrote in message news:22969851-7a52-4463-ba7b-f25fad578232@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Right -- thanks. I guess I'd feel better about the views solution if.
it were only a few tables from this database that were referenced in
our stored procs. Then we'd have the fairly reasonable task of
creating just a couple of views -- one for each of those tables that
we need to reference. But we need something to replace the old dbname
with the new dbname generally, since there are often many tables
queried from this other database.
Out of curiosity, do other folks hard-code database names in stored
procedures? Well, this is only relevant in situations like ours where
one or two of your JOIN tables is on a different database than where
your proc is. I'm curious how others handle this situation. (Well,
I'm guessing that not many companies are still using SQL 2000 like us,
where the SYNONYM command is not available.)
On Dec 10, 6:45 pm, "m" <m...@xxx> wrote:In brief, your choices are:
- dynamic SQL (perf hit)
- find & replace in the source when the name changes (re-testing required)
- views (small perf hit)
"Rick" <rick0...@xxxxxxxxxxx> wrote in message
news:MPG.258a1238a472d0be989918@xxxxxxxxxxxxxxxxxxxxxxx
> We have a SQL 2000 database currently named WAREHOUSE.
> At some point in the future this will be given a
> different name.
> We are creating a stored procedure that sits on another
> database called RECEIPT on the same server. Each proc
> has a long and complex SELECT statement that queries
> many tables on RECEIPT, but then joins to one table on
> this Warehouse database.
> I could hard-code the dot notation:
> SELECT ...
> FROM ....
> JOIN WAREHOUSE.DBO.MYTABLE
> and then change this when the database gets its new
> name, but that's bad programming. Management likes the
> idea of passing the dbname in as an argument and using
> dynamic SQL, but I really don't want to change the
> entire proc to use dynamic SQL just for this one table.
> The job runs without administrator privileges.
> Any suggestions on how to do this? Using SQL 2000 I
> feel limited. Thanks for any help- Hide quoted text -
- Show quoted text -
- References:
- Variable-named database in a proc
- From: Rick
- Re: Variable-named database in a proc
- From: m
- Re: Variable-named database in a proc
- From: Rick
- Variable-named database in a proc
- Prev by Date: Re: Need help with join syntax and SQL select
- Next by Date: Re: Create Adjancency Model From Flat Data?
- Previous by thread: Re: Variable-named database in a proc
- Next by thread: Re: Variable-named database in a proc
- Index(es):
Relevant Pages
|
