1

Is it possible to run multiple statements inside a if on sql server?

For example if a column does not exists on a table i what to add a column, apply an update and alter this column again. it appears to do not work, how can i do it?

IF NOT EXISTS(SELECT * FROM sys.columns WHERE 
    [name] = N'myTable' AND 
    [object_id] = OBJECT_ID(N'myNewColumn'))
BEGIN

    ALTER TABLE dbo.myTable ADD myNewColumn VARCHAR(5) NULL;        
    UPDATE dbo.myTable SET myNewColumn = '';        
    ALTER TABLE dbo.myTable ALTER COLUMN myNewColumn VARCHAR(5) NOT NULL;

END

On my temporary solution i've used the EXECUTE statement to run the ALTER's and UPDATE, but i does not seems to be a very elegant solution.

any idea?

podiluska
  • 50,950
  • 7
  • 98
  • 104
Flavio CF Oliveira
  • 5,235
  • 13
  • 40
  • 63
  • 1
    Yes, using `EXECUTE` is the correct approach. This is how SSMS will generate scripts as well. The issue is not with multiple statements inside an `IF` block...the issue is with multiple 'DDL' statements that alter the schema. Most often these must be 'the first statement in a batch'. – mdisibio May 23 '14 at 04:49

0 Answers0