I am trying to create a stored procedure to add a new column into a database from an ASP web application.
The SQL Code for this is:-
ALTER TABLE tablename
ADD COLUMN columnname columndatatype
I have tried to insert this SQL into a stored procedure:-
CREATE PROCEDURE sp_addcolumn
-- Add the parameters for the stored procedure here
@tablename varchar(50) = 0,
@columnname varchar(50) = 0,
@datatype varchar (50) = 0
ALTER TABLE @tablename
ADD COLUMN @columnname @datatype
when i try to execture this i get an error message:- "Msg 102, Level 15, State 1, Procedure sp_addcolumn, Line 7 Incorrect syntax near '@tablename'."
How I want this to work is - The user chooses which table they want to add a new column to from a drop down box which holds all the table names then they insert the column name and datatype.
I'm not sure what i have done wrong here, if anyone has any ideas they would be greatly appreciated! Thanks!
I am getting the error:- "Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][SQL Native Client][SQL Server]The definition for column ' ' must include a data type. /sqlsite/adminaddnewcolumn.asp, line 51"
You will find you are missing a space between columnname and datatype, which is there in my code but somehow you have removed it when implementing it in your project.
I always do a select first when concatenating dynamic sql to see the resulting string first before executing anything, beacuase the syntax often obscures little bugs like this, especially when quotes need to be part of the string!!
Your second attempt tells me you don't grok the square brackets  these are in case anyone tries to create a column name with a space in it.
Also you have now introduced spaces between the [ and the table name, you need to remove those, same for the column name unless you want lots of subtle bugs in your app cos the column names have leading spaces in their names. USE MY CODE TO TO THE LETTER and you will not have any problems. It's important to make these mistakes though otherwise you would not have learned the importance of white space in some parts of TSQL syntax. Remember computers are not like us, they are stupifyingly exacting !! one byte out of place and you're stiffed.