Dynamic SQL to alter table and add column

Question

_{Wednesday, September 2, 2009 5:17 PM}

Hi,

Alter table test2
add column ID int

this is what I want to do via dynamic sql...because the table name will be @tablename input from user.

I am trying out some examples in dynamic sql.
Below is a small SP.

It would give me error when i try to update. Error saying id is invalid..

ALTER Procedure testdynamic2
@TableName VarChar(100)
AS

Declare @SQL VarChar(1000)
Declare @vary varchar(100)
Declare @final varchar(1000)

SELECT @SQL = 'ALTER TABLE '
SELECT @SQL = @SQL + @TableName
select @vary = 'add column ' + id
select @final = @sql + @vary

Exec ( @final)
GO

Could you please guide me here.

All replies (5)

_{Wednesday, September 2, 2009 5:37 PM ✅Answered}

alter Procedure testdynamic2
@TableName VarChar(100)
AS
set nocount on
Declare @SQL VarChar(1000)
Declare @vary varchar(100)
Declare @final varchar(1000)

SELECT @SQL = 'ALTER TABLE '
SELECT @SQL = @SQL + @TableName
select @vary = ' add id int'
select @final = @sql + @vary
--select @final
Exec ( @final)
GO

Varsham Papikian, New England SQL Server User Group Executive Board, USA
New England SQL Server User Group; My LinkedIn page
Please remember to click the "Mark as Answer" button if a post helps you!

---

_{Wednesday, September 2, 2009 5:35 PM}

I tried this: still error near @id and @ table.

error:
Incorrect syntax near '@id'.
Msg 137, Level 15, State 2, Procedure testdynamic2, Line 11
Must declare the scalar variable "@TableName".

ALTER Procedure testdynamic2
@TableName VarChar(100)
@id int
AS

Declare @SQL VarChar(1000)
Declare @vary varchar(100)
Declare @final varchar(1000)

SELECT @SQL = 'ALTER TABLE '
SELECT @SQL = @SQL + @TableName
select @vary = 'add column ' + @id
select @final = @sql + @vary

Exec ( @final)
GO

---

_{Wednesday, September 2, 2009 5:40 PM}

Thank you.

---

_{Wednesday, September 2, 2009 5:42 PM}

You are welcome. Please use the button 'Mark as answer' :-) 

Remember - if you add another param which has INT type, for example, use CAST(id as varchar) to convert it to string before cancatenating.

---

Varsham Papikian, New England SQL Server User Group Executive Board, USA
New England SQL Server User Group; My LinkedIn page
Please remember to click the "Mark as Answer" button if a post helps you!

---

_{Monday, June 4, 2012 3:32 PM}

How about sql injection?

Can this prevent sql injection??

Het