Bifurcate Table Name

[RON]

The tables in a SQL Server 7.0 database is preceded by the company
code of a company. For e.g. the companies Microsoft, Dell & Nokia have
the same tables in the DB - Table1, Table2 & Table3 but for Microsoft,
the names of the 3 tables are MSTable1, MSTable2 & MSTable3.

Similarly, the names of the 3 tables for Dell are DLTable1, DLTable2 &
DLTable3. An ASP script gets the company codes from the previous page.
The company codes are assigned to an ASP variable named 'strCCode' (of
course, without the quotes).

I am passing the value of the variable strCCode to a stored procedure.
This is how I framed the stored procedure:

========================
CREATE PROCEDURE MyProc
@CCode varchar(10)
--some more variables here
AS
SELECT * FROM @CCode + 'Table1' AS tbl1
JOIN @CCode + 'Table2' AS tbl2
ON.........
========================

But the above SP generates the error

Incorrect syntax near '@CCode'

pointing to the SELECT....line.

What am I doing wrong here?

But if I use dynamic SQL & change the above SP to this:

========================
CREATE PROCEDURE MyProc
@CCode varchar(10)
--some more variables here
AS
DECLARE
@sql varchar(8000)

SET @sql='SELECT * FROM ' + @CCode + 'Table1 AS tbl1
JOIN ' + @CCode + 'Table2 AS tbl2
ON.........'

EXEC (@sql)
========================

Then the above SP doesn't generate any errors but the question is is
using dynamic SQL a sane approach?

[David Portas]

"RON" <rn...@rediffmail.com> wrote in message
news:2b20ac31-01fe-4e4d...@s8g2000prg.googlegroups.com...

> The tables in a SQL Server 7.0 database is preceded by the company
> code of a company. For e.g. the companies Microsoft, Dell & Nokia have
> the same tables in the DB - Table1, Table2 & Table3 but for Microsoft,
> the names of the 3 tables are MSTable1, MSTable2 & MSTable3.
>
> Similarly, the names of the 3 tables for Dell are DLTable1, DLTable2 &
> DLTable3. An ASP script gets the company codes from the previous page.
> The company codes are assigned to an ASP variable named 'strCCode' (of
> course, without the quotes).
>
> I am passing the value of the variable strCCode to a stored procedure.
> This is how I framed the stored procedure:
>

Why would you do such a thing? It just seems like an incredibly stupid
design.

It is also bad practice to use SELECT * in production-quality code.

--
David Portas

[Andrew J. Kelly]

Yes if you proceed with a design such as that you will be forced to use
dynamic sql for all calls to these types of tables. Why would you do
something like that in the first place? Can you just add a company code
column to each table and use that as a filter? What about having a separate
db for each company and a common schema in each? At least that way all you
need to do is point to the correct db and all the calls are the same.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"RON" <rn...@rediffmail.com> wrote in message
news:2b20ac31-01fe-4e4d...@s8g2000prg.googlegroups.com...

[RON]

On Feb 17, 6:47 pm, "Andrew J. Kelly" <sqlmvpnooos...@shadhawk.com>
wrote:

> Yes if you proceed with a design such as that you will be forced to use
> dynamic sql for all calls to these types of tables. Why would you do
> something like that in the first place? Can you just add a company code
> column to each table and use that as a filter?  What about having a separate
> db for each company and a common schema in each? At least that way all you
> need to do is point to the correct db and all the calls are the same.
>
> --
> Andrew J. Kelly    SQL MVP
> Solid Quality Mentors
>

> "RON" <r...@rediffmail.com> wrote in message

> > using dynamic SQL a sane approach?- Hide quoted text -
>
> - Show quoted text -

I completely agree with both of you as far as design is concerned. It
is indeed very very poor but unfortunately I just can't do anything
about it since a lot of work has already gone into using the faulty
design. I didn't design it & am not even aware of who has done it. So
please bear with the design for the time being.

So isn't there any way other than dynamic SQL in such a scenario?

>>It is also bad practice to use SELECT * in production-quality code

Used * just for brevity.....I know it isn't a good practice.....

Thanks,

Ron

[Andrew J. Kelly]

No dynamic sql is your only path if you want to use a common set of code.
You can create x different sets of stored procedures in which each set is
directly responsible for a specific customer. That way you just call the
correct sp and no dynamic sql is needed. But while this may be an option if
you only have a few companies you will find it difficult to maintain as the
number grows. In any case make sure you use sp_executesql instead of exec()
so you can at least take advantage of plan reuse by utilizing the
parameters. You may want to have a look here:

http://www.sommarskog.se/dynamic_sql.html

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"RON" <rn...@rediffmail.com> wrote in message

news:079c7738-d0e6-4a08...@u10g2000prn.googlegroups.com...

[Erland Sommarskog]

RON (rn...@rediffmail.com) writes:
> I completely agree with both of you as far as design is concerned. It
> is indeed very very poor but unfortunately I just can't do anything
> about it since a lot of work has already gone into using the faulty
> design. I didn't design it & am not even aware of who has done it. So
> please bear with the design for the time being.
>
> So isn't there any way other than dynamic SQL in such a scenario?

No, the alternative is to create views that goes:

SELECT Company = 'MS', col1, col2, ...
FROM MSTable1
UNION ALL
SELECT Company = 'DL', co11, col2, ...
FROM DLTable1
....

Although this is not going to work out if you have very many companies,
as SQL Server only permits 256 tables per query (SQL 2005; I think it's
the same in SQL 7, but I don't know for sure).

Yet an alternative is to write a tool that generates the procedure,
so that you get MSMyProc, DLMyProc etc. The application would call the
main procedure that would go:

CREATE PROCEDRUE MyProc @CCode varchar(10) AS
DECLARE @spname sysname
SELECT @spname = @CCcode + 'MyProc'
EXEC @spname

I can agree that this may not be wotrh the hassle. Then again, dynamic
SQL requires that users have direct permissions to the tables, and if
you cannot accept that this could be a viable solution.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Mike C#]

I agree with Andrew, this is an absolutely horrible design. Whoever designed
it has stuck you with using dynamic SQL or creating a custom hack to work
around the most obvious design flaw. That said, here's exactly why your
example won't work: you cannot parameterize object names in SQL statements.

In your example:

SELECT * FROM @CCode + 'Table1' AS tbl1
JOIN @CCode + 'Table2' AS tbl2

You're trying to use a variable name/expression in place of an object/table
name. This is not allowed. So the question was, is dynamic SQL the "sane"
approach? This database design it not "sane" to begin with, so dynamic SQL
might be considered less insane than some other approaches. But the only
"sane" course of action would be to fix this mess before it gets even worse.

"RON" <rn...@rediffmail.com> wrote in message

news:2b20ac31-01fe-4e4d...@s8g2000prg.googlegroups.com...

[David Portas]

Surely you CAN do something about it. At the very least you could put your
concerns in writing to the management responsible for it. That's what I'd do
anyway - but maybe that's just me. I've never been one who can sit quietly
and tolerate crap. ;-)

Erland's suggestion is also a good one. Once you have created the view you
may be able to migrate painlessly to a better design by replacing the view
with a table and replacing the legacy collection of tables with views.

--
David Portas

[Rick Sawtell]

"David Portas" <REMOVE_BEFORE_R...@acm.org> wrote in message
news:7t6dnYmRGfsHBSXa...@giganews.com...

Here here,

I've got to agree with these two as well. This poor design smaks of an
Access hack, or worse, a FileMaker Pro conversion.

Put your concerns in writing. Show how difficult this is going to be
maintenance-wise, security-wise, coding-wise etc.

Views could buy you some time to get things fixed properly.

Rick Sawtell

[Henrik Staun Poulsen]

RON,

As Erland mentions, you may have a problem with the 250! tables in a
query limit of SQL Server.
There is another way round the problem:

1) Create a new table that holds all the data, and with a company
identifier, and move all your data here.
CREATE TABLE Table1 (Company char(2), Col1 int, Col2 whatever...)
2) Drop the old tables
3) Create one view per company with the same name as the old tables.
(MSTable1, DLTable1)
CREATE VIEW MSTable1 as SELECT Col1, col2 FROM Table1 where
Company = 'MS' go

Now you have a solution that can take you forward, and let you move
your code onto the new data format in your own time.

Best regards,

Henrik Staun Poulsen

[--CELKO--]

This design is so bad it has a name -- attribute splitting. You need
a company identifier in the tables, then you can use VIEWs or
queries.

Would you split your Personnel Table into {male, female}? or {tall,
short} ?

[Erland Sommarskog]

Brilliant!

[Mike C#]

INSERT INTO MSTable1 (Col1, Col2)
VALUES (12345, 67890)

?

"Henrik Staun Poulsen" <h...@stovi.com> wrote in message
news:6de189cc-cab2-4599...@72g2000hsu.googlegroups.com...

[Henrik Staun Poulsen]

Mike C#,

> INSERT INTO MSTable1 (Col1, Col2) VALUES (12345, 67890

Good question.
Would an "INSTEAD OF Trigger" work?
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1050a88e-d15f-4f0e-
baee-a63f379f6529.htm

I have not tried yet, as our data collection software was rewritten at
the same time as the table format change.

Best regards,
Henrik

[Henrik Staun Poulsen]

Erland,

Thank you very much. The sun just came out.

Henrik

[jhof...@googlemail.com]

Didn't David suggest exactly the same thing just a few posts
earlier? :)

[Hugo Kornelis]

On Tue, 19 Feb 2008 04:39:16 -0800 (PST), Henrik Staun Poulsen wrote:

>Mike C#,
>
>> INSERT INTO MSTable1 (Col1, Col2) VALUES (12345, 67890
>Good question.
>Would an "INSTEAD OF Trigger" work?

Hi Henrik,

It would.

CREATE TRIGGER tr_ins_MSTable1
ON MSTable1 INSTEAD OF INSERT
AS
INSERT INTO Table1 (Company, Col1, Col2)
SELECT 'MS', Col1, Col2
FROM inserted;
go

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Mike C#]

Ditto for updates. My thing was that these might be some things the OP
needs to consider before implementing the big view (temporary) solution.
That, and making sure that it's only a temporary solution :)

"Henrik Staun Poulsen" <h...@stovi.com> wrote in message

news:04b2494c-35b9-4bcd...@q70g2000hsb.googlegroups.com...

[Henrik Staun Poulsen]

Hi Mike, Hugo

>>That, and making sure that it's only a temporary solution

A bad table design is a temporary solution, only question is; how long
is temporay? Surprisingly long :-)

>>CREATE TRIGGER tr_ins_MSTable1 ...
Thank you. Would it be possible to write just one trigger, or do you
need one for update, and one for inserts and one for deletes?

Best regards,
Henrik

[Hugo Kornelis]

Hi Henrik,

In this case, you only need one for inserts. For updates, the company
column is not exposed in the view and can hence never change. And for
deletes - well, there's not much left in the table to change the company
column of after the delete, is there? :)

[David Portas]

<jhof...@googlemail.com> wrote in message
news:dce60fea-037d-4ec0...@u72g2000hsf.googlegroups.com...

> Didn't David suggest exactly the same thing just a few posts
> earlier? :)

I did. But Henrik explained it much better.

--
David Portas

[--CELKO--]

>> A bad table design is a temporary solution, only question is; how long is temporary? Surprisingly long :-) <<

There is nothing more permanent than a temporary patch :) My favorite
is optimizer hints that never leave.