Sql Server Stored Procedure for adding columns to a table -

i m trying create stored procedure add 2 columns onto end of table. sp takes 3 inputs, table name, state, , school year.

when run sp error:

the name "ca" not permitted in context. valid expressions constants, constant expressions, , (in contexts) variables. column names not permitted. 

here sp:

create procedure sp_addscoolenrollyear      @state nvarchar(2),        @shcoolyear nvarchar(15),     @tablename nvarchar(100) begin     set nocount on;     declare @sql nvarchar(max);  set @sql=   n'alter table ' + quotename(@tablename) +                 n'add stateenrollid varchar(2) default ' + quotename(@state) + n'not null,                      schoolyearkey varchar(20) default ' + quotename(@shcoolyear) + n'not null'      execute sp_executesql @sql end go 

this how execute:

[dbo].[sp_addscoolenrollyear] n'ca', n'2014-2015' , n'[dbo].[test_copy]' 

you adding square brackets around table name, , need use ' instead of square brackets around strings, , more spaces between words, , each column added needs separate alter:

create procedure sp_addscoolenrollyear      @state nvarchar(2),        @shcoolyear nvarchar(15),     @tablename sysname begin     set nocount on;     declare @sql nvarchar(max);  set @sql=n'alter table ' + @tablename         + n' add stateenrollid varchar(2) default ''' + @state + n''' not null;' +char(10)        + n'alter table ' + @tablename         + n' add schoolyearkey varchar(20) default ''' + @shcoolyear + n''' not null;'     select  @sql;     execute sp_executesql @sql; end  end go exec [dbo].[sp_addscoolenrollyear] n'ca', n'2014-2015' , n'[dbo].[test_copy]' 

code generated:

alter table dbo.test_copy add stateenrollid varchar(2) default 'ca' not null; alter table dbo.test_copy add schoolyearkey varchar(20) default '2014-2015' not null; 

rextester demo: http://rextester.com/hekc53724