CODEWITHSUNDEEP
sql-servert-sqlssmsalter-table
Charlotte45
Charlotte45

Reputation: 143

ALTER TABLE...ADD not working in SSMS

I'm trying to alter a table by adding a new column inside a stored procedure. Here's what I have:

USE [Lab5]
go
create procedure do_ver2 as
    alter table VehicleContract
        add trailerNr varchar(7) null;
    alter table VehicleContract
        add constraint FK_VehicleContract_Trailers foreign key (trailerNr) references Trailers(trailerNr);
go

The problem is... although it doesn't show any errors, it also doesn't create a new column either. Any idea why? It's T-SQL.

Upvotes: 0

Views: 1483

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You need to use Dynamic-SQL to execute DDL statement inside stored procedure:

CREATE PROCEDURE do_ver2 AS
BEGIN
    EXEC('alter table VehicleContract add trailerNr varchar(7) null;');
    EXEC('alter table VehicleContract add constraint FK_VehicleContract_Trailers foreign key (trailerNr) references Trailers(trailerNr);');
END
GO

EDIT:

In your code without BEGIN and END your stored procedure contain only first statement. You should always add begin/end block.

USE [Lab5]
go
create procedure do_ver2 as
    EXEC('alter table VehicleContract
        add trailerNr varchar(7) null;');
    EXEC('alter table VehicleContract
        add constraint FK_VehicleContract_Trailers 
        foreign key (trailerNr) references Trailers(trailerNr);');
go

If you check the the code of stored procedure you will see that it have only: 

 create procedure do_ver2 as
        EXEC('alter table VehicleContract add trailerNr varchar(7) null;');

Upvotes: 1

Related Questions