Ad:

MS SQL Discussion Thread
Unsolved
Views: 12119

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

May 23rd, 2008

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

Expand Post »

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

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

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.

Similar Threads

Populating a dropdown with data from an SqlServer stored procedure in ASP.NET
memory management in wndows 2000 in Windows NT / 2000 / XP
How can i retrieve data after i logod on..... in ASP.NET
UPDATE question for ms sql 2000 in MS SQL

GLT

Reputation Points: 18

Solved Threads: 0

Junior Poster in Training

Offline

98 posts
since Aug 2007

Permalink

May 23rd, 2008

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

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.

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)

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

Permalink

May 23rd, 2008

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

Thanks for your quick reply!

I now have this:-

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

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

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

GLT

Reputation Points: 18

Solved Threads: 0

Junior Poster in Training

Offline

98 posts
since Aug 2007

Permalink

May 23rd, 2008

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

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

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:

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.

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

Permalink

May 23rd, 2008

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

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

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

GLT

Reputation Points: 18

Solved Threads: 0

Junior Poster in Training

Offline

98 posts
since Aug 2007

Permalink

May 23rd, 2008

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

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.

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

Permalink

May 27th, 2008

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

what i have now is:-

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

GLT

Reputation Points: 18

Solved Threads: 0

Junior Poster in Training

Offline

98 posts
since Aug 2007

Permalink

May 27th, 2008

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

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

Spot the difference:

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

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

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

Permalink

May 27th, 2008

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

Sorry for making you pull your hair out!

You've been a great help!

What i have now is:-

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

GLT

Reputation Points: 18

Solved Threads: 0

Junior Poster in Training

Offline

98 posts
since Aug 2007

Permalink

May 27th, 2008

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

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

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

Page 1
2
Next

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Run a stored procedure for every product in a products table

Next Thread in MS SQL Forum Timeline: Finding Specific Strings in Another Strings [not charindex]

DaniWeb Message

Cancel Changes

User Name
Password