MS SQL

MS SQL RSS

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

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Aug 2007

Posts: 98

Reputation: GLT is an unknown quantity at this point

Solved Threads: 0

GLT

Offline

Junior Poster in Training

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

May 23rd, 2008

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
 ALTER TABLE tablename
ADD COLUMN columnname columndatatype
 ALTER TABLE tablename
ADD COLUMN columnname columndatatype

I have tried to insert this SQL into a stored procedure:-

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
 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
 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

Last edited by GLT; May 23rd, 2008 at 10:04 am.

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: alter table stored procedure...???...

May 23rd, 2008

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.

 Help with Code Tags 
 tsql Syntax (Toggle Plain Text) 
 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)
 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)

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Aug 2007

Posts: 98

Reputation: GLT is an unknown quantity at this point

Solved Threads: 0

GLT

Offline

Junior Poster in Training

Re: alter table stored procedure...???...

May 23rd, 2008

Thanks for your quick reply!

I now have this:-

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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)
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:-

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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)
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:-

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
<%@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()
%>
<%@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

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: alter table stored procedure...???...

May 23rd, 2008

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

 Help with Code Tags 
 tsql Syntax (Toggle Plain Text) 
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
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:

 Help with Code Tags 
 tsql Syntax (Toggle Plain Text) 
EXEC sp_addcolumn 'tablename', 'columnname', 'datatype'
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.

Last edited by hollystyles; May 23rd, 2008 at 12:01 pm.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Aug 2007

Posts: 98

Reputation: GLT is an unknown quantity at this point

Solved Threads: 0

GLT

Offline

Junior Poster in Training

Re: alter table stored procedure...???...

May 23rd, 2008

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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!!!!

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: alter table stored procedure...???...

May 23rd, 2008

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.

Last edited by hollystyles; May 23rd, 2008 at 1:09 pm.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Aug 2007

Posts: 98

Reputation: GLT is an unknown quantity at this point

Solved Threads: 0

GLT

Offline

Junior Poster in Training

Re: alter table stored procedure...???...

May 27th, 2008

what i have now is:-

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: alter table stored procedure...???...

May 27th, 2008

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

Spot the difference:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
--CORRECT CODE
SET @tsql = 'ALTER TABLE [' + @tablename + '] ADD [' + @columnname + '] ' + @datatype
--CORRECT CODE
SET @tsql = 'ALTER TABLE [' + @tablename + '] ADD [' + @columnname + '] ' + @datatype

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
--SYNTAX ERROR !!!
SET @tsql = 'ALTER TABLE ['+@TABLE_NAME+'] ADD ['+@columnname+']'+@datatype
--SYNTAX ERROR !!!
SET @tsql = 'ALTER TABLE ['+@TABLE_NAME+'] ADD ['+@columnname+']'+@datatype

==========================================
Yadda yadda yadda...
Web junky, fevered monkey

•

Join Date: Aug 2007

Posts: 98

Reputation: GLT is an unknown quantity at this point

Solved Threads: 0

GLT

Offline

Junior Poster in Training

Re: alter table stored procedure...???...

May 27th, 2008

Sorry for making you pull your hair out!

You've been a great help!

What i have now is:-

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: alter table stored procedure...???...

#10

May 27th, 2008

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

Last edited by hollystyles; May 27th, 2008 at 11:28 am.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey