[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored procedure to add new column to table

Posted on 2009-02-20
3
Medium Priority
?
1,657 Views
Last Modified: 2012-08-13
Hi,

I would like a stored procedure to add new column to a table.
The new column name must be passed as a parameter to s.p.. I thought the below would work with I get an incorrect syntax message at @NewColumnName.  @NewColumnName is the name of the new column to create - this will change so is a parameter.

Thanks in advance for help.


LTER PROCEDURE [dbo].[CreateColumn]
	-- Add the parameters for the stored procedure here
	@NewColumnName varchar(300)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Create new table
	ALTER TABLE HistorianData
	ADD @NewColumnName float                        --incorrect syntax
END

Open in new window

0
Comment
Question by:rwallacej
3 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1000 total points
ID: 23692426
would have to be like this:

ALTER PROCEDURE [dbo].[CreateColumn]
      -- Add the parameters for the stored procedure here
      @NewColumnName varchar(300)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 declare @n nvarchar(3000)
set @n = 'ALTER TABLE HistorianData
      ADD ' + @NewColumnName + ' FLOAT'

exec sp_executesql @n
END
0
 
LVL 13

Assisted Solution

by:sm394
sm394 earned 1000 total points
ID: 23692455
this may help

 declare @NewColumnName nvarchar(30)
   set @NewColumnName='Test2'
  declare @sql nvarchar(max)
   set @sql='ALTER TABLE Test  ADD '+ @NewColumnName +' float'  
  exec  (@sql)
 
0
 

Author Closing Comment

by:rwallacej
ID: 31549244
thank-you very much for your help
regards, rwallacej
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question