| Author |
Topic |
|
dmilam
Posting Yak Master
185 Posts |
 Posted - 2010-03-23 : 18:32:58
|
| Not to discuss table variables, but to discuss whether the name of a table can be declared as a string for substitution throughout a scriptdeclare @tablename as varchar(10)set @tablename = 'tablename';create table @tablename (id int not null)insert into @tablename ...--obviously a table is not the same data type as varchar, but CAST doesn't seem to work for this kind of conversion |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-03-23 : 18:47:10
|
| It can. You would need to use dynamic SQL and execute it.Read up on dynamic SQL.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2010-03-23 : 19:06:13
|
| Thanks, good to know! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-03-25 : 02:58:45
|
| But make sure you read this article fullywww.sommarskog.se/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2010-08-02 : 17:12:33
|
Creating a synonym is perfect for this. Exciting!  http://www.sommarskog.se/dynamic_sql.html#Dyn_DBhttp://msdn.microsoft.com/en-us/library/ms177544.aspx |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-08-02 : 18:43:55
|
| In a properly designed schema, a table models a set of ONE AND ONLY ONE KIND entities. Switching the names in and out as parameter would be like doing the same thing to automobiles, squids, etc. A magical generic makes no sense. If you really want to write bad code, you can use dynamic SQL. --CELKO--Joe Celko, SQL Guru |
|
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2010-08-03 : 13:19:59
|
quote:
In a properly designed schema, a table models a set of ONE AND ONLY ONE KIND entities. Switching the names in and out as parameter would be like doing the same thing to automobiles, squids, etc. A magical generic makes no sense. If you really want to write bad code, you can use dynamic SQL.
Should a table name never be the name of a required string used elsewhere in the query? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-08-03 : 19:45:33
|
| >>Should a table name never be the name of a required string used elsewhere in the query? <<A table name cannot be a reserved word.A table name should be a collective or plural noun for the set of entities it models. For example:Employee = badEmployees = betterPersonnel = bestBut the bad news is that a table name can be the same a column name or variable. It is a flaw in the language. Do not write code that way. --CELKO--Joe Celko, SQL Guru |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-08-04 : 11:42:39
|
quote:
Originally posted by jcelko
>>Should a table name never be the name of a required string used elsewhere in the query? <<A table name cannot be a reserved word.A table name should be a collective or plural noun for the set of entities it models. For example:Employee = badEmployees = betterPersonnel = bestBut the bad news is that a table name can be the same a column name or variable. It is a flaw in the language. Do not write code that way.
Joe, I don't want to get into the Singular versus Plural table names. As I have my own opinion and I think people that have a very solid relation background are, mostly, of the same opinion. However, I've seen you toss around the 11179 ISO standard for naming and I can only find them naming Classes, which I assume to be the equivalent of an Entity and they do them as singular names. So, why would you give out bad information about how to name Entities? When I say "bad" I'm referring to Emploees being better than Employee. |
|
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2010-08-06 : 00:21:33
|
Example:-- Set name of current shipmentDECLARE @ShipmentName AS VARCHAR(25);SET @ShipmentName = 'Shipment1876';-- Create a synonym for the table for the current calling userDECLARE @Sql NVARCHAR(200)SET @Sql = 'DROP SYNONYM TBL;CREATE SYNONYM TBL FOR T.['+(SELECT USER_NAME())+'].'+@ShipmentNameEXEC sp_executesql @Sql Here the shipment name will vary each time the code needs to be run. (In a few weeks, the shipment name will be different). @ShipmentName appears elsewhere in the query because it will become a column name in a spreadsheet. It is not a column name in any table. |
|
|
|
|
Can the name of a table be a variable?