Can we execute multiple Alter statements in an IF else block for One tbale

Question

Can we execute multiple Alter statements in an IF else block for One tbale

SQLTestUser

Hall of Fame

Points: 3997
More actions
September 15, 2010 at 11:07 am

#387792

Rt not i am performing Alter statements for a tabe one at a time as shown below, I have to add 4 columns to the same table only if they donot exist and if they do i show a message as below, is there a way to perform 4 alters within the same block ?? Thanks for the help
IF EXISTS(SELECT * FROM sys.columns WHERE [name] = 'A' AND OBJECT_NAME([object_id]) = 'TableName')
Begin
Print 'The Column A already exists'
Select * from TableName
END
ELSE
Begin
ALTER TABLE TableName ADD A DATETIME NULL
Print 'Added the column A to the TableName'
Select * from TableName
End

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

sure, you can do it two ways;

the BEGIN...END block can contain more than one command, and the ALTER TABLe command can take a comma delimited lit of columsn tadd:

IF EXISTS(SELECT * FROM sys.columns WHERE [name] = 'A' AND OBJECT_NAME([object_id]) = 'TableName')

BEGIN

Print 'The Column A already exists'

Select * from TableName

END

ELSE

BEGIN

ALTER TABLE TableName ADD A DATETIME NULL

ALTER TABLE TableName ADD B VARCHAR(10) NULL

ALTER TABLE TableName ADD C int,D money,E VARCHAR(30)

Print 'Added the column A to the TableName'

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 2

Thanks for the script but my problem is that i have to check if column A exists than add BCD if B exists add ACd and so on, and there is no way we can accomplish that right,

1) so add ABCD to a table only if ABCD donot exist

2) Add any of the column if they donot exist

3) if all exist show " aLL already exist"

i will work on something but if you already know please let me know

thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Sounds like you are asking if you can four logical checks within the same if statement and then do some logic that changes for each condition. You are correct in that you can't do this in one if block.

You will need to check for each column since the code will be unique to that column's existence. What you could do i create 4 bit variables to track which (if any) of the columns needed to be added.

_______________________________________________________________

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/

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 4

how can i accomplish that can we declare without creating a procedure in sQL. I hae somefamiliarilt with PL/SQl procedures but not SQL server which is partly why i am struggling.

If u can direct me towards something that would be awesome

thanks Again

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Given that this is adding columns and such i will assume that this a one time use need. Just create your script in SSMS and then save it so you can execute it on your production server.

declare @Col_A_Added bit = 0

declare @Col_B_Added bit = 0

declare @Col_C_Added bit = 0

declare @Col_D_Added bit = 0

if col_A...

begin

alter statement here

print 'Col A added'

set @Col_A_Added = 1

end

if col_B...

begin

alter statement here

print 'Col B added'

set @Col_B_Added = 1

end

if col_C...

...

if col_D...

...

then you could add something like

if @Col_A = 0 and @Col_B = 0 and @Col_C = 0 and @Col_D = 0 begin

print 'All columns existed or no columns were added'

end

Sorry for the pseudocode but hopefully this will point you in the right direction.

_______________________________________________________________

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/