SQL Server Performance

Execute - drop

Discussion in 'T-SQL Performance Tuning for Developers' started by luma, Aug 9, 2005.

  1. luma New Member

    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_'+@novaTab ( @novaTab is name of table, is changing during run of code)

    If (Select object_id(@detail)) > 0

    Set @sqlCommand='Drop Table ' +@detail
    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.



    luma, Aug 9, 2005
    #2

  2. Madhivanan Moderator

    Did you run it and did you get any error?


    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Aug 9, 2005
    #2

  3. FrankKalis Moderator

    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)
    FrankKalis, Aug 9, 2005
    #2

  4. luma New Member

    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
    luma, Aug 9, 2005
    #2

  5. Madhivanan Moderator

    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
    Madhivanan, Aug 9, 2005
    #2

  6. luma New Member



    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 ' +@detail
    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
    luma, Aug 9, 2005
    #2

  7. Madhivanan Moderator

    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
    Madhivanan, Aug 9, 2005
    #2

  8. luma New Member

    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
    luma, Aug 9, 2005
    #2

  9. Madhivanan Moderator

    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
    Madhivanan, Aug 9, 2005
    #2

  10. Adriaan New Member

    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.
    Adriaan, Aug 9, 2005
    #2

  11. luma New Member

    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.


    luma, Aug 9, 2005
    #2

  12. Adriaan New Member

    Take the first part of the script that you posted:

    If (Select object_id(@detail)) > 0

    Set @sqlCommand='Drop Table ' +@detail
    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 ' +@detail
    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.
    Adriaan, Aug 9, 2005
    #2

  13. Madhivanan Moderator

    >>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
    Madhivanan, Aug 9, 2005
    #2

  14. luma New Member

    Thank you Adrian and Madhivanan too.
    Lubo
    luma, Aug 9, 2005
    #2

  15. Madhivanan Moderator

    &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
    Madhivanan, Aug 9, 2005
    #2

  16. luma New Member

    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
    luma, Aug 9, 2005
    #2

Share This Page