1,105,295 Community Members

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

Member Avatar
GLT
Junior Poster in Training
98 posts since Aug 2007
Reputation Points: 8 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
hollystyles
Veteran Poster
1,179 posts since Feb 2005
Reputation Points: 113 [?]
Q&As Helped to Solve: 78 [?]
Skill Endorsements: 0 [?]
 
0
 

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)
Member Avatar
GLT
Junior Poster in Training
98 posts since Aug 2007
Reputation Points: 8 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
hollystyles
Veteran Poster
1,179 posts since Feb 2005
Reputation Points: 113 [?]
Q&As Helped to Solve: 78 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
GLT
Junior Poster in Training
98 posts since Aug 2007
Reputation Points: 8 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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!!!! :)

Member Avatar
hollystyles
Veteran Poster
1,179 posts since Feb 2005
Reputation Points: 113 [?]
Q&As Helped to Solve: 78 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
GLT
Junior Poster in Training
98 posts since Aug 2007
Reputation Points: 8 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
hollystyles
Veteran Poster
1,179 posts since Feb 2005
Reputation Points: 113 [?]
Q&As Helped to Solve: 78 [?]
Skill Endorsements: 0 [?]
 
0
 

(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
Member Avatar
GLT
Junior Poster in Training
98 posts since Aug 2007
Reputation Points: 8 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
hollystyles
Veteran Poster
1,179 posts since Feb 2005
Reputation Points: 113 [?]
Q&As Helped to Solve: 78 [?]
Skill Endorsements: 0 [?]
 
0
 

(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.

Member Avatar
hanuman308
Newbie Poster
2 posts since Sep 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
safacon
Newbie Poster
1 post since Jun 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
triptikumbhat@g
Newbie Poster
5 posts since Mar 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article