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?
> 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.
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.
> 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?
> 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
> > 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?- 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.....
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:
> 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
> > 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?- 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.....
RON (r...@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:
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
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.
> 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?
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.
> 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
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.
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.
> 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.
Brilliant!
-- Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
> 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.
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 :)
>> 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.
On Wed, 20 Feb 2008 04:01:58 -0800 (PST), Henrik Staun Poulsen wrote: >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?
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? :)
