How can I group these SQL statements into one batch so I execute all at once?

Question

I have few changes I am making to the schema of the table in order to incorporate the encryption.

1. Alter table: adding columns

   ALTER TABLE Demo
   ADD name_Encrypted VARBINARY(MAX)
   

2. Add encrypted value in this field based on field I am encrypting.

   OPEN symmetric KEY symkey decryption BY assymetric KEY asym_key 
   
   UPDATE demo 
   SET name_encrypted = Encryptbykey(Key_guid('Symkey'), NAME) 
   FROM demo 
   
   CLOSE symmetric KEY symkey
   

3. DROP unencrypted column

   ALTER TABLE Demo DROP COLUMN Name
   

4. Rename the encrypted column to original name

   EXEC Sp_rename 'Demo.Name_encrypted','Name'
   

If I put them all in a stored procedure, I get an error saying "name_Encrypted column doesn't exist" even though I created one in the first step.

How can I have all these statements in one batch, so I can just run it once and all of them execute?

Answer 1

I think you've got the opposite problem you think. You can't do these all in one batch/transaction/etc - because you're trying to add to a column that hasn't been added yet. Realistically, you need all of those to be separate transactions.

I think the answer you're looking for is to add 'GO' between each of those steps, to tell SQL to finish processing the prior section before proceeding onward. Add the column, 'GO' to tell SQL to finish adding the column before trying to go onward; populate the field, 'GO' to tell SQL to finish populating the field before continuing. Drop a field, 'GO', etc.

Answer 2

Add a GO statement after each DDL statement, to commit it to the DB engine. See the following link