alter table stored procedure...???...

[GLT]

Hi,

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
AS
ALTER TABLE @tablename
 ADD COLUMN @columnname @datatype
END

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!

GLT

[hollystyles]

Alter table can't take dynamic parameters. To do this you will have to concatenate the whole TSQL command as a string dynamically and then execute it. Also you don't need the COLUMN keyword.

CREATE PROCEDURE sp_addcolumn 
	-- Add the parameters for the stored procedure here
	@tablename varchar(50) = 0, 
	@columnname varchar(50) = 0,
	@datatype varchar (50) = 0

AS

set @tsql = 'ALTER TABLE [' + @tablename + '] ADD [' + @columnname + '] ' + @datatype 

exec(@tsql)

[GLT]

Thanks for your quick reply!

I now have this:-

CREATE PROCEDURE sp_addcolumn 
	-- Add the parameters for the stored procedure here
	@TABLE_NAME varchar(50) = 0, 
	@columnname varchar(50) = 0,
	@datatype varchar (50) = 0

AS
DECLARE @tsql varchar (200)
SET @tsql = 'ALTER TABLE [ '+@TABLE_NAME+'] ADD ['+@columnname+']' + @datatype
EXEC(@tsql)

I have also tried this:-

CREATE PROCEDURE sp_addcolumn 
	-- Add the parameters for the stored procedure here
	@TABLE_NAME varchar(50) = 0, 
	@columnname varchar(50) = 0,
	@datatype varchar (50) = 0

AS
DECLARE @tsql varchar (200)
SET @tsql = 'ALTER TABLE [ '+@TABLE_NAME+'] ADD ['+@columnname+@datatype+']'
EXEC(@tsql)

and my ASP code is:-

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/Silverwingdatabase.asp" -->
<%

Dim cmd_addcolumn__TABLE_NAME
cmd_addcolumn__TABLE_NAME = " "
if(Request("TABLE_NAME") <> "") then cmd_addcolumn__TABLE_NAME = Request("TABLE_NAME")

Dim cmd_addcolumn__columnname
cmd_addcolumn__columnname = " "
if(Request("columnname") <> "") then cmd_addcolumn__columnname = Request("columnname")

Dim cmd_addcolumn__datatype
cmd_addcolumn__datatype = " "
if(Request("datatype") <> "") then cmd_addcolumn__datatype = Request("datatype")

%>
<%
set cmd_addcolumn = Server.CreateObject("ADODB.Command")
cmd_addcolumn.ActiveConnection = MM_Silverwingdatabase_STRING
cmd_addcolumn.CommandText = "dbo.sp_addcolumn"
cmd_addcolumn.Parameters.Append cmd_addcolumn.CreateParameter("@RETURN_VALUE", 3, 4)
cmd_addcolumn.Parameters.Append cmd_addcolumn.CreateParameter("@TABLE_NAME", 200, 1,50,cmd_addcolumn__TABLE_NAME)
cmd_addcolumn.Parameters.Append cmd_addcolumn.CreateParameter("@columnname", 200, 1,50,cmd_addcolumn__columnname)
cmd_addcolumn.Parameters.Append cmd_addcolumn.CreateParameter("@datatype", 200, 1,50,cmd_addcolumn__datatype)
cmd_addcolumn.CommandType = 4
cmd_addcolumn.CommandTimeout = 0
cmd_addcolumn.Prepared = true
cmd_addcolumn.Execute()
%>

Sorry about all the code but i am very confused!

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"

Any ideas greatly appreciated! Thanks Guys!!!
GLT

[hollystyles]

Here's a tip: amend the stored proc to select @tsql instead of EXEC(@tsl)

CREATE PROCEDURE sp_addcolumn 
	-- Add the parameters for the stored procedure here
	@TABLE_NAME varchar(50) = 0, 
	@columnname varchar(50) = 0,
	@datatype varchar (50) = 0

AS
DECLARE @tsql varchar (200)
SET @tsql = 'ALTER TABLE [ '+@TABLE_NAME+'] ADD ['+@columnname+']' + @datatype
SELECT @tsql

OPen QueryAnalyser or Ms Sql Management Studio (whatever you're using) connect to the DB and open a new query window put:

exec sp_addcolumn 'tablename', 'columnname', 'datatype'

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.

[GLT]

I keep getting the same error. I tried taking the datatype part out and putting an actual datatype in the code but i got this:-

"Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '0'.
/sqlsite/adminaddnewcolumn.asp, line 47"

I dont understand!

I used this code:-

CREATE PROCEDURE sp_addcolumn 	
-- Add the parameters for the stored procedure here	
@TABLE_NAME VARCHAR(50) = 0, 
@columnname VARCHAR(50) = 0,
@datatype VARCHAR (50) = 0 
AS
DECLARE @tsql VARCHAR (200)
SET @tsql = 'ALTER TABLE [ ' + @TABLE_NAME + '] ADD [ ' + @columnname + ' ] ' + @datatype
SELECT @tsql

and also changed SELECT back to EXEC which also did not work.

The code for the stored proc seems to work its when I go to run the web page im getting these errors but the error points to SQL Server and I cant see a problem with my ASP code.

Thanks again for your help!!!! :)

[hollystyles]

The table must exist in the database.

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.

[GLT]

what i have now is:-

CREATE PROCEDURE sp_addcolumn 	
-- Add the parameters for the stored procedure here	
@TABLE_NAME VARCHAR(50) = 0, 	
@columnname VARCHAR(50) = 0,	
@datatype VARCHAR (50) = 0
AS
DECLARE @tsql VARCHAR (200)
SET @tsql = 'ALTER TABLE ['+@TABLE_NAME+'] ADD ['+@columnname+']'+@datatype
EXEC @tsql
GO

I am following your code which is why i dont understand where i have gone wrong. I have take the spaces out.

the error that i am getting now is:-

'Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][SQL Native Client][SQL Server] Incorrect syntax near '0''

Any ideas would be great!

Thanks!
GLT

[hollystyles]

(Holly pulls his hair out ....) I see an important space disappeared in my second post! gah! ok use this:

Spot the difference:

--CORRECT CODE
SET @tsql = 'ALTER TABLE [' + @tablename + '] ADD [' + @columnname + '] ' + @datatype

--SYNTAX ERROR !!!
SET @tsql = 'ALTER TABLE ['+@TABLE_NAME+'] ADD ['+@columnname+']'+@datatype

[GLT]

Sorry for making you pull your hair out! :$
You've been a great help!

What i have now is:-

CREATE PROCEDURE sp_addcolumn 	
-- Add the parameters for the stored procedure here	
@TABLE_NAME VARCHAR(50) = 0, 	
@columnname VARCHAR(50) = 0,	
@datatype VARCHAR (50) = 0
AS
DECLARE @tsql VARCHAR (200)
SET @tsql = 'ALTER TABLE [' + @TABLE_NAME + '] ADD [' + @columnname + '] ' + @datatype 
EXEC @tsql
GO

and getting the error:-
The name 'ALTER TABLE [] ADD []' is not a valid identifier.

I tried removing the [] but i got the error message:-
Could not find stored procedure 'ALTER TABLE ADD'

I must be doing something wrong...??...

Sorry again!!
GLT

[hollystyles]

(Holyl is now bald as a coot)

I think EXEC @tsql needs to be EXEC(@tsql) with parenthesis.

and getting the error:-
The name 'ALTER TABLE [] ADD []' is not a valid identifier.

To me this means you're passing zero length strings for the @TABLE_NAME and @columnname parameters.

[hanuman308]

hi there
i am getting the error
Cannot alter table ' EP16SEP_MX_MNLD' because this table does not exist in database 'SIL_TESTDB'.

whenever i try to execute the SP
but table is actully exist in database

[safacon]

Dear
I have this problem too ehith these codes.
I wanna execute this procedure in VB.net.would you please share your solution?
best regards

[triptikumbhat@g]

ALTER PROCEDURE insert_column

-- Add the parameters for the stored procedure here

@columnname as VARCHAR(max),
@datatype as VARCHAR (max)
AS
DECLARE @SQL nvarchar(500)
DECLARE @Colm nvarchar(50)
DECLARE @type nvarchar(50)

SET @Colm = @columnname
SET @type = @datatype

SET @SQL = 'ALTER TABLE test ADD '+@colm+' '+@type+'
DEFAULT 0'
EXEC sp_executesql @SQL,N'@columnname as VARCHAR(max),
@datatype as VARCHAR (max)',@columnname,@datatype