Safayat Zisan Safayat Zisan - 3 months ago 8
SQL Question

How to pass a parameter in stored procedure while dynamically adding columns in a table

I am getting a syntax error near @coll in alter statement. I have added a column in the same way, but not by any parameter. How can I do the same with a parameter?

create procedure sp1
@coll nvarchar(50)
as
if not exists(select * from sys.columns where Name = N'coll' and Object_ID = Object_ID(N'Students'))

begin
Alter table Students ADD @coll NULL
end

Answer

You have to do this with dynamic SQL:

create procedure sp1 (
    @coll nvarchar(50) 
) as
begin
    if not exists(select * from sys.columns where Name = @coll and Object_ID = Object_ID(N'Students'))

    begin 
        declare @sql nvarchar(max);
        set @sql = 'Alter table Students ADD [' + @coll + '] NULL';
        exec(@sql);
    end;
end;

Note: I changed the if to use the variable. Otherwise you are always checking for the name 'coll'.

Or, better yet:

        set @sql = 'Alter table Students ADD ' + quotename(@coll) + ' NULL';