Add new table column using Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Add new table column using Stored Procedure

Author

Topic

sqlsic
Starting Member

4 Posts

Posted - 2004-08-19 : 12:07:40

Hi,

Can you please put me on the right track on this. I need to add a new column to an existing table using data supplied via a web form.
I am using the following basic code but I keep getting an error in the sql statement "Line 13: Incorrect syntax near @colname". What is the right way to do this? Thank you in advance.

CREATE PROCEDURE [dbo].[SpUserRights]
@rightname varchar(50)

AS

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tbl_group')

BEGIN
DECLARE @colname varchar(50)
SET @colname = @rightname

ALTER TABLE dbo.tbl_group ADD @colname BIT DEFAULT 0
END

ELSE

RETURN 1
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-19 : 12:10:46

try this:
exec ('ALTER TABLE dbo.tbl_group ADD ' + @colname + ' BIT DEFAULT 0')

Go with the flow & have fun! Else fight the flow :)

sqlsic
Starting Member

4 Posts

Posted - 2004-08-20 : 06:31:56

Thanks "spirit1". Worked wonderfully.
Good day!

quote:
Originally posted by spirit1

try this:
exec ('ALTER TABLE dbo.tbl_group ADD ' + @colname + ' BIT DEFAULT 0')

Go with the flow & have fun! Else fight the flow :)

sqlsic
Starting Member

4 Posts

Posted - 2004-08-20 : 08:05:41

Hi there,
thank you for your earlier useful response. I thought you may be able to help on this one as well.(I feel like going with the flow!)

It would be nice that my stored procedure will check whether the suplied column name already exists in the schema prior to adding it to the table. Based on this it will return the apropriate parameter.

How do I obtain a list of column names in a table?
The looping and name verification I hope I can do myself. I do appreciate your help.

quote:
Originally posted by spirit1

try this:
exec ('ALTER TABLE dbo.tbl_group ADD ' + @colname + ' BIT DEFAULT 0')

Go with the flow & have fun! Else fight the flow :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-20 : 08:10:20

this will give you a set of all columns in a given table:

SELECT column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'TableName'

but since you're in a flow (feels good right? :))
this will give you a csv string of columns in a given table:
Declare @ColumnList varchar(1000)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'TableName'
select @ColumnList

hope it helps

Go with the flow & have fun! Else fight the flow :)

sqlsic
Starting Member

4 Posts

Posted - 2004-08-20 : 11:50:36

That's great. Thanks a lot!

quote:
Originally posted by spirit1

this will give you a set of all columns in a given table:

SELECT column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'TableName'

but since you're in a flow (feels good right? :))
this will give you a csv string of columns in a given table:
Declare @ColumnList varchar(1000)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'TableName'
select @ColumnList

hope it helps

Go with the flow & have fun! Else fight the flow :)

neelp
Starting Member

1 Post

Posted - 2004-09-01 : 12:51:42

Hi,

Is it possible to add multiple columns at a time using Alter Table?

i.e.
Alter Table Orders
Add (Select ProductName from Products), varchar(100)

Thanks,
Neel

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-01 : 13:09:03

this is the way to do it.

Alter Table Orders
Add ProductName varchar(100), ProductName1 varchar(100), ProductName2 varchar(100)

this of course won't put data into the orders from ProductName as i got a feeling u wanted that. it will only create a column.
to put data in you need to update the column after you inserted it.

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources