Problem with alter table stored procedure...

[lidds]

I don't know if this is possible, but what I want to have is a stored procedure that I can pass the name of the column that I wish to be added to my table from my vb.net application.

 

example: from my vb.net app I would call the stored procedure and pass 'column1' to it and then within the stored procedure it would alter the table and add a new column called 'column1' to it.

 

So the stored procedure would look something like this:

 

CREATE PROCEDURE [dbo].[spAddColumn] @columnName as varChar(50)
AS
ALTER TABLE myTbl ADD @columnName varChar(50)
GO

 

The above SQL statement gives me an error:

Error 170: Line 3: Incorrect syntax near '@columnName'.

 

Is this possible??? And if so could someone please point out what I am doing wrong.

 

Thanks in advance

 

Simon

[Nerseus]

You'll have to use some dynamic SQL for this to work and manually piece in the column name. No guarantees that will work but that's what I'd try. It would be something like this:

CREATE PROCEDURE [dbo].[spAddColumn] @columnName as varChar(50)
AS
DECLARE @GoodTableName varchar(255)
SET @GoodTableName = '[' + REPLACE(@columnName, '''', '''''') + ']'
exec ('ALTER TABLE myTbl ADD ' + @GoodTableName+ ' varChar(50)')

 

-ner

[lidds]

Thanks I will give this a go tomorrow, bit late for me to attempt new stuff :p

 

Cheers

 

Simon

[lidds]

Can't thank you enough Nerseus,

 

It worked like a charm, and has given me ideas on how to acheive some other stuff as well.

 

Simon