CODEWITHSUNDEEP
sqlsql-serverstored-procedures
spitfiredd
spitfiredd

Reputation: 3135

Sql Server Stored Procedure for adding columns to a table

I m trying to create a stored procedure that will add two columns onto the end of a table. The SP takes 3 inputs, a table name, a state, and a school year.

When I run the SP I get an error:

The name "CA" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Here is my SP:

CREATE PROCEDURE sp_addScoolEnrollYear 
    @state nvarchar(2),   
    @shcoolYear nvarchar(15),
    @tableName nvarchar(100)
AS
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 is how I execute:

[dbo].[sp_addScoolEnrollYear] N'CA', N'2014-2015' , N'[dbo].[test_copy]'

Upvotes: 2

Views: 1432

Answers (1)

SqlZim
SqlZim

Reputation: 38023

You were adding extra square brackets around the table name, and you need to use ' instead of square brackets around your strings, and some more spaces between words, and each column added needs to be a separate alter:

CREATE PROCEDURE sp_addScoolEnrollYear 
    @state nvarchar(2),   
    @shcoolYear nvarchar(15),
    @tableName sysname
AS
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

Upvotes: 1

Related Questions