Problem with alter table stored procedure...

L

lidds

Well-known member
Joined
Nov 9, 2004
Messages
210
I dont 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:

Code: 
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
 
Youll have to use some dynamic SQL for this to work and manually piece in the column name. No guarantees that will work but thats what Id try. It would be something like this:
Code: 
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
 
Thanks I will give this a go tomorrow, bit late for me to attempt new stuff :p

Cheers

Simon
 
Cant thank you enough Nerseus,

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

Simon
 
Back
Top