Professional Web Applications Themes

ALTER and UPDATE in store procedure - Microsoft SQL / MS SQL Server

> How come SQL skip running the ALTER TABLE command > first and executes the UPDATE command? Nothing is executing either command. The pr is checking the existing table, while reading the UPDATE statement, to make sure it's valid. Since it can't find that column name in the table definition (since the ALTER command has not yet run), it won't accept the UPDATE statement as valid. Why are you adding columns on the fly like this? Doesn't seem like a very well-thought-out design... There are several workarounds, you could use dynamic SQL, or you could create a second procedure that ...

  1. #1

    Default Re: ALTER and UPDATE in store procedure

    > How come SQL skip running the ALTER TABLE command
    > first and executes the UPDATE command?
    Nothing is executing either command. The pr is checking the existing
    table, while reading the UPDATE statement, to make sure it's valid. Since
    it can't find that column name in the table definition (since the ALTER
    command has not yet run), it won't accept the UPDATE statement as valid.

    Why are you adding columns on the fly like this? Doesn't seem like a very
    well-thought-out design...

    There are several workarounds, you could use dynamic SQL, or you could
    create a second procedure that performs the update (and *create* the
    procedure *while* the column exists).
    > It will not allow another 'GO' command)
    GO is a batch separator, not a T-SQL command.



    Aaron Bertrand - MVP Guest

  2. #2

    Default Re: ALTER and UPDATE in store procedure

    Thank you for the explanation. It is clear to me now.
    I will try to keep in mind to have all the columns created
    first before running UPDATE.

    Jane K.
    Jane Guest

Similar Threads

  1. Store Procedure Oracle
    By Hatichitic in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 18th, 06:48 AM
  2. Return value from store procedure
    By Ken Schaefer in forum ASP
    Replies: 9
    Last Post: August 23rd, 09:58 AM
  3. Access database from store procedure
    By in forum ASP Database
    Replies: 1
    Last Post: July 21st, 11:09 AM
  4. Store Procedure plus parameter where condition..
    By Boris Condarco in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 01:54 PM
  5. Store Procedure Help
    By Karsten Farrell in forum Oracle Server
    Replies: 3
    Last Post: January 11th, 04:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139