| Author |
Topic |
|
rushdib
Yak Posting Veteran
93 Posts |
 Posted - 2003-11-10 : 10:35:49
|
| Hi,Is there any way to make the table name a variable in select, insert, update or delete statements?ex:declare @tableName nvarchar(50)set @tableName = 'table1'select * from @tableNameThanks,Rushdi |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-10 : 11:09:34
|
| See dynamic sql and exec or sp_executesqlI'm sure there's an article about this on this site.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-11-10 : 11:52:13
|
| [code]declare myTable varchar(20)set myTable = 'myTable'execute('select * from ' + @mytable + ' where blah blah')[/code]__________________Make love not war! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-10 : 12:36:58
|
| Rushdi, you should try to avoid dynamic sql. It will negatively impact performance. You also have to provide explicit table permissions, so security is also a problem. If you let us know what your requirement is, we could help you write a query that doesn't use dynamic sql.Tara |
|
|
|
rushdib
Yak Posting Veteran
93 Posts |
Posted - 2003-11-10 : 12:48:55
|
| Hi Tara,We have 10 linked servers (Access databases) in a SQL Server. The Access databases are for different deptartments. The table names are same in all those Access databases.I have to write some queries to get the data from the tables in linked server to SQL tables and update those tables where I got the data from.I am using the following command to refer linked tables.Linked Server Name...tableNameMy queries (1 insert and 2 update)are same except the Linked Server Name. This is the reason I want to use dynamic SQL, so I don't have to repeat my queries changing the Linked Server Name.My other alternative is to connect the Linked Server dynamically.Let me know a good way to do this so it won't bring down the perfomance.Thanks,Rushdi |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-10 : 12:59:05
|
| It is recommended that you repeat your queries. You will be negatively impacted by performance if you don't. Repeating queries is not bad programming. Dynamic sql can be considered bad programming if there is a workaround.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2003-11-10 : 16:36:11
|
| There is a tradeoff to consider though. If this is not intensive from a CPU level, and you aren't running tight on hardware then I would take the ease of code any day of the week. The big things to look for are how many records are being considered here, and how complex the join is. If you are just moving an entire table with a few hundred records, it's better to have one procedure. If not, then you should make a query for each, so you get a performance increase by having a solid execution plan and limiting the overhead of recompiles. Profiler is a great tool to research this type of issue.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-10 : 16:47:45
|
quote:
Originally posted by derrickleggett
There is a tradeoff to consider though. If this is not intensive from a CPU level, and you aren't running tight on hardware then I would take the ease of code any day of the week.
I disagree. If the stored procedure or query is to be part of an application, then non-dynamic sql should be used if possible. Of course there are times when dynamic sql can not be avoided. Dynamic sql should not be used just because of lazy programming. You wouldn't do it on the front end, so why do it in the back end?BTW, when I saw lazy programming I don't mean to suggest a lazy programmer. Some programmers think that dynamic sql is neater because it is less lines of code. If you've ever had to debug dynamic sql, then you'll know how un-neat it can be.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2003-11-11 : 09:01:29
|
| And that's the tradeoff Tara. If it's a simple insert, any DBA or SQL developer worth a penny could figure it out and debug it. I would take that one insert as dynamic SQL before I "repeat" my query over 500 times and have to change it in 500 places for one change. It doesn't sound like we're talking about complicated code here; otherwise, I would agree with you. Dynamic SQL isn't for lazy programmers. It's for programmers who can weigh they needs of the business correctly and decide on the best solution. You don't spend hours on repeating code when you can build a dynamic solution in a percentage of the time that is easy to maintain. Tools aren't bad. How you use them can be.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
variable table names