Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kanati
Starting Member
36 Posts |
 Posted - 2002-11-21 : 15:42:26
|
| I have a number of routines that use a # temp table or a ## temp table and my boss has decided that we don't want to do this. He wants to use a regular table that's created at the beginning of a stored proc and dropped at the end. Personally I think he got dumped off the loony truck, but I don't have any real argument against it. I'm hoping you guys can inform me. |
|
|
burbakei
Yak Posting Veteran
80 Posts |
Posted - 2002-11-21 : 15:59:10
|
| so if two or more users start the sp at almost the same time, only the first one can go to the end of sp. the other users get the this errorThere is already an object named 'ANY_TABLE' in the database. |
 |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-11-21 : 16:13:15
|
| There are quite a few arguments NOT to do such thing(s), but I have serious doubts that your boss will listen.Just prove that he is wrong by doing what he tells you to do. |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-11-21 : 17:04:11
|
| Tell him that, within stored procedures, you have to refer to normal tables with a preceding "at" sign: @TEMPTABLE.Cheers-b |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-11-21 : 17:09:18
|
| burbakei's explanation is what you should tell your boss. If more than one stored procedure gets execute, only one of them will complete successfully. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-11-21 : 22:16:48
|
| Will your stored proc ever get executed by two people / processes at once ? If not, then it shouldn't be a problem.Did he give any explaination for why he wanted it this way ? Did you ask for one ?Damian |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-11-21 : 22:50:15
|
| If you have sql2k.If your boss is taking all the credit, giving you no raises and doesn't know what he is doing.First make your boss think he was right then tell him you'll get rid of all the temp tables. Then watch as the smirk from his face drops when you tell him that his idea has flaws but you have a better idea which is to use table variables.If he still insists do what he asks (take your sweet ass time) and when it fails just rub it in that you told him (wait until his boss is there).Even better is to do what he asks and when it fails mention a better solution in an email which you cc to every important person in the company.If you're boss is nice and gives you credit and nice raises then just suggest the improvement if he still wants to go his way then just have the table variables ready so you can have his back when he fails.For backup here are some articles.http://www.sqlteam.com/item.asp?ItemID=9454http://www.eggheadcafe.com/articles/20010823.asphttp://www.sql-server-performance.com/temp_tables.asphttp://www.tek-tips.com/gfaqs.cfm/lev2/4/lev3/27/pid/183/fid/1100Edited by - ValterBorges on 11/21/2002 23:25:16 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-11-22 : 06:34:55
|
quote:
Personally I think he got dumped off the loony truck.
I would go with that.You have temporary tables and permanent tables. Nuf said?Ignoring query plans, multi user, leaving table due to crash, table IDs, updating system tables, backup strategies,.... it should just sound like a bad idea.Permanent table with a spid as part of the key maybe.Maybe it should be written in access which is happy with this sort of thing - the boss might be more comfortable with that.==========================================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.Edited by - nr on 11/22/2002 06:39:27 |
|
|
|
|
|
|
|
|
TEMPTABLE vs #TEMPTABLE