Adding new column from inside a store procedure....

My question is...
Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULLThe only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach to
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:
> My question is...
> Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
--
http://www.aspfaq.com/
(Reverse address to reply.)