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?
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 SQL MVP
Solid Quality Mentors
"RON" <rn...@rediffmail.com> wrote in message
news:2b20ac31-01fe-4e4d...@s8g2000prg.googlegroups.com...
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
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...
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
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...
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.
Rick Sawtell
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
Would you split your Personnel Table into {male, female}? or {tall,
short} ?
?
"Henrik Staun Poulsen" <h...@stovi.com> wrote in message
news:6de189cc-cab2-4599...@72g2000hsu.googlegroups.com...
> 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
Thank you very much. The sun just came out.
Henrik
>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
"Henrik Staun Poulsen" <h...@stovi.com> wrote in message
news:04b2494c-35b9-4bcd...@q70g2000hsb.googlegroups.com...
>>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
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? :)
I did. But Henrik explained it much better.
--
David Portas
There is nothing more permanent than a temporary patch :) My favorite
is optimizer hints that never leave.