SELECT * FROM @TableName ?????
-
I have a multi-company environment, where each company has a related table inside one database. So, I have multiple tables (one for each company) with the same structure.
I need a way to use variables inside stored procedures to manipulate data, for example (Table: [01_EMPLOYEES])
...
SET @TableName='['+@IdCompany+'_EMPLOYEES'
SELECT * FROM @TableName WHERE LastName=@LastName
...
INSERT INTO @TableName (Name, LastName) VALUES (@Name, @LastName)
But it doesn't work !!!
I've tried to build a string with the SQL sentence and use it with the EXEC method, but it doesn't work either... It seems to have troubles with the arguments (parameters) of the SP (dates, numbers, etc)...
Any ideas?
-
Should work. I usually build up an @sql variable (or whatever name you give it) and print it, try running it in QA to make sure it's valid syntax - easy to miss a single quote or something.
Alternative is to put the stored proc into each of the databases. Yes, you end up with a lot of copies of the same code (or mostly same code), but you get compiled code instead of dynamci.
-
To add to Andy, you should really prefer having multiple small sp_procs rather than passing such a parameter to one generic sp_proc. Why? This should explain it.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Actually, you should rethink this design. You are violating Codd's "Information Rule" by using the table identifier to convey information. This will lead to many more kludges like this. You should instead add a column to each table to identify company, or use a separate database for each company.
--Jonathan -
Thousand posts!!
Big congrats to you, Jonathan
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Congrats from us here at SSC as well. A thousand posts is a lot of time invested in your profession (and our community) - bravo!
Re your comment about Codd, I think thats a fair point. Breaking db rules often leads to pain. Doesn't mean you shouldnt, does mean you shouldnt do it lightly.
-
Sergio,
This sounds like an opportunity to use the partitioned views feature of SQL Server instead of going with the dynamic sql hack. I agree with Jonathan that violating this data integrity rule shouldn't be done lightly. However, you might still make this situation viable by using an updateable view against these underlying implementation tables. Check out these introductory articles on how to leverage these new 2k features: Instead of Triggers. Horizontally Partitioned Views, Index Views.
Peter Evans (__PETER Peter_)
Creating Horizontal Partitioned Views
http://www.winnetmag.com/Article/ArticleID/8234/8234.html
instead of triggers
http://www.winnetmag.com/Article/ArticleID/15791/15791.html
Introducing Index Views
http://www.winnetmag.com/Article/ArticleID/8410/8410.html
Creating Index Views
http://www.winnetmag.com/Article/ArticleID/26605/26605.html
-
I'm confused... wouldn't it just be a lot easier to put all of the companies' information in a single table with a CompanyID column? Isn't that what 3rd normal form is all about?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi, guys...
Thanks a lot for your answers... Let me check all the alternatives that you gave me, and I'll talk to you later...
Bye !!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply