creating a SP that add columns into a table

Hi all,

i want to create a stored procedure that will add columns into a table but i have a problem using a parameter in this context, for example:

- SP --

-- PARAMS --

declare @.TableName nvarchar(30)

declare @.colName nvarchar(30)

declare @.colType nvarChar(30)

-- Actual Code

alter table @.TableName

add @.colName @.colType

it looks as though the paramater cannot be used in this context or i should do something in order to make sense of this code.. please help me :)

Z

You have to use Dynamic SQL here..

- SP --

-- PARAMS --

declare @.TableName nvarchar(30)

declare @.colName nvarchar(30)

declare @.colType nvarChar(30)

-- Actual Code

Declare @.SQL as Varchar(1000);

Select @.SQL = 'alter table ' + @.TableName + ' add ' + @.colName + ' ' + @.colType

Exec (@.SQL)

|||

Hi Zacky,

Here is a possible solution:

create procedure AddColumnToTable
(
@.tableName varchar(50),
@.columnName varchar(50),
@.dataType varchar(50)
)
as
begin
declare @.sql varchar(4000)
set @.sql = 'ALTER TABLE ' + @.tableName + ' ADD ' + @.columnName + ' ' + @.dataType
exec sp_sqlexec @.sql
end

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Sorry MandiD, I didn't see that you posted an answer while I was creating mine.

|||

What kind of error you are getting here..

R u missed any code ..reposting the code again

Begin
declare @.TableName nvarchar(30)
declare @.colName nvarchar(30)
declare @.colType nvarChar(30)
declare @.SQL as NVarchar(1000);
-- Actual Code
--Select @.TableName='Sample', @.colName='newCol1', @.colType='Int'

Select @.SQL = N'alter table ' + @.TableName + ' add ' + @.colName + ' ' + @.colType
Exec (@.SQL)
--or use
--Exec sp_executesql @.SQL
End

|||

thanks looks great!!

Z