error when add and update in sp

  • CREATE PROCURE w AS

    ALTER TABLE t ADD x char(1)

    UPDATE t set x =1

    Even when it lets me create that stored procedure (if I create it when x exists), when it runs, there is an error on the UPDATE statement because column x doesn't exist.

    What's the conventional way to deal with this, it must come up all the time? I can work around it by putting the UPDATE inside EXEC, is there another/better way?

    Thanks

  • karunakar2351 (10/7/2013)


    CREATE PROCURE w AS

    ALTER TABLE t ADD x char(1)

    UPDATE t set x =1

    Even when it lets me create that stored procedure (if I create it when x exists), when it runs, there is an error on the UPDATE statement because column x doesn't exist.

    What's the conventional way to deal with this, it must come up all the time? I can work around it by putting the UPDATE inside EXEC, is there another/better way?

    Thanks

    Actually this type of thing is not all that common. What happens when you run the stored proc a second time? The column x will already exist so it will fail when adding the new column.

    If there is some other way you are dealing with that you will have to execute the update statement inside of dynamic sql. The update will not be able to be compiled at run time because the column does not yet exist.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • you can make this on this way

    alter procedure w AS

    exec('ALTER TABLE t ADD x char(1)')

    exec('UPDATE t set x = 1')

    But you can run only once. next time will be error. Column alredy exists

  • SrcName (10/7/2013)


    you can make this on this way

    alter procedure w AS

    exec('ALTER TABLE t ADD x char(1)')

    exec('UPDATE t set x = 1')

    But you can run only once. next time will be error. Column alredy exists

    You can add a NOT EXISTS so this can be run more than once.

    alter procedure w AS

    if NOT exists (select * from sys.columns where name = 'x' and object_id = object_id('t'))

    ALTER TABLE t ADD x char(1)

    exec('UPDATE t set x = 1')

    But the real question is why? DDL should not be performed inside of a stored proc. Certainly not something like this. If you want to add the column, then just add the column. Then your proc can do whatever it need to do without all the extra silliness.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (10/7/2013)


    SrcName (10/7/2013)


    you can make this on this way

    alter procedure w AS

    exec('ALTER TABLE t ADD x char(1)')

    exec('UPDATE t set x = 1')

    But you can run only once. next time will be error. Column alredy exists

    You can add a NOT EXISTS so this can be run more than once.

    alter procedure w AS

    if NOT exists (select * from sys.columns where name = 'x' and object_id = object_id('t'))

    ALTER TABLE t ADD x char(1)

    exec('UPDATE t set x = 1')

    But the real question is why? DDL should not be performed inside of a stored proc. Certainly not something like this. If you want to add the column, then just add the column. Then your proc can do whatever it need to do without all the extra silliness.

    yes Sean, that is ok, thank you. I'm pleased to learn with you.

    I referred specifically on attached question.

  • karunakar2351 (10/7/2013)


    ALTER TABLE t ADD x char(1)

    BUt why the same column should get added with EVERY SP call ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply