Execute – drop | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Execute – drop

Hi, i need to drop and rename the tables during run of code. If table is exist I need drop it.
How can I make a sql statement for test of existing table set @detail=’Det_’[email protected] ( @novaTab is name of table, is changing during run of code) If (Select object_id(@detail)) > 0 Set @sqlCommand=’Drop Table ‘ [email protected]
Execute @sqlCommand) set @sqlCommand=’ sp_rename Detail , ‘ + @detail (table Detail is creating on start of cycle) Execute (@sqlCommand)
set @detail=” .
.
. I think It is need write statement @novaTab as some execute.
Did you run it and did you get any error?
Madhivanan Failing to plan is Planning to fail
This seems to be very closely related to your other two threads
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9620
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9648 Can we assume that your problems there have been solved? If not, please provide feedback there so that someone can help. Having that said, usually you would use some kind of
IF OBJECT_ID(‘your_table’) > 0 or something similar to achieve that. Btw, you can also look at how SQL Server Enterprise Manager itself does that, when you generate a script of an existing table. Personally I think your whole approach is, to say the least optimizable. Creating, deleting an/or renaming database objects isn’t something that should be done as a normal course of action. If you don’t know thishttp://www.sommarskog.se/dynamic_sql.html you should really read it. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

When table(s) are exist before start of code – no problem ..
Det_v_4
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to ‘Det_v_4’.
Det_v_3
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to ‘Det_v_3’.
But when tables not exist , there is an error Server: Msg 15335, Level 11, State 1, Procedure sp_rename, Line 342
Error: The @newname value ‘Det_v_4’ is already in use as a object name and would cause a duplicate that is not permitted.
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to ‘Det_v_3’. because droppin don’t execute
Do you need to do this really?
Can you give more details on why you are doing these dynamically?
Madhivanan Failing to plan is Planning to fail
I make with two or more tables and xml files.
Steps are : – reading table Obsah , there are names of xml files – import xml file -> some_table_1
– work with some_table_1
If (Select object_id(@detail)) > 0 Set @sqlCommand=’Drop Table ‘ [email protected]
Execute @sqlCommand) set @sqlCommand=’ sp_rename Detail , ‘ + @detail (table Detail is creating on start of cycle) Execute (@sqlCommand)
set @detail=” set @sqlCommand=’ sp_rename Detail , ‘ + @detail
Execute (@sqlPrikaz)
And again reading table Obsah …. But there is wrong test of exist file , or missing Begin – End .
Excuse me, I’m not good in english, Lubo
The General Syntax of If is If <condition>
Begin
–some works End
else
Begin
–some works End Madhivanan Failing to plan is Planning to fail
Always when tables don’t exist I have a message with error Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table ‘Det_v_3’, because it does not exist in the system catalog.
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to ‘Det_v_3’. Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table ‘Det_v_4’, because it does not exist in the system catalog.
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to ‘Det_v_4’. but then objects are rename
What is the point of renaming the table? Give more details on what you are trying to do Cant you use temporary tables in the Stored procedure so that it will be automatically dropped after execution?
Madhivanan Failing to plan is Planning to fail
Like Madhivanan pointed out, you need BEGIN and END around a block of lines that should only run on the condition that the IF statement is true. If you do not use BEGIN and END, only the first statement immediately after the IF statement is subject to the condition, any statement after the first one will be executed unconditionally. IF ‘a’ = ‘b’
PRINT ‘This is true’
PRINT ‘This is false’ You will see that both statements are executed.
During run of code I am making with table Detail. In some step i need to rename Detail as Some_table . Names of Some_table are different. Before rename I need test if Some_table exist. When Some_table exists and I rename Detail as Some_table there is a problem. If Some_table exists I need drop it and to rename
set @sqlPrikaz=’ sp_rename Detail , ‘ + Some_table
Execute (@sqlPrikaz) If isn’t exist I can’t drop it but only rename it. I don’t know write correct clause for this bit of code.

Take the first part of the script that you posted: If (Select object_id(@detail)) > 0 Set @sqlCommand=’Drop Table ‘ [email protected]
Execute @sqlCommand) set @sqlCommand=’ sp_rename Detail , ‘ + @detail
This must obviously be changed to:
If (Select object_id(@detail)) > 0
BEGIN
Set @sqlCommand=’Drop Table ‘ [email protected]
Execute @sqlCommand)
END
ELSE
BEGIN

set @sqlCommand=’ sp_rename Detail , ‘ + @detail
END
EDIT: You can drop the second set of BEGIN and END, as there is only a single line under the ELSE condition. Thanks Madhivanan, I should have paid closer attention.
>>During run of code I am making with table Detail. In some step i need to rename Detail as Some_table Why?
If (select * from information_schema.tables where
table_name=’Some_table’ and table_type=’Base table’)
–table exists drop it else
–Rename
Instead of that you can copy the data to other table and do calculation based on other table
You should tell us your exact requirement
Madhivanan Failing to plan is Planning to fail
Thank you Adrian and Madhivanan too.
Lubo

&gt;&gt;Thanks Madhivanan, I should have paid closer attention.<br /><br />No problem. This happens to all [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />&gt;&gt;Thank you Adrian and Madhivanan too.<br /><br />Did you solve your problem?<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Yes, i need just it. It’s a pitty I don’t know english better . Maybe I could describe my problem more clear. I’m new in SQL. Lubo
]]>