Add a column to table from stored proc

Hello all,

I want to alter a table in a stored proc to add a column dynamically. I am passing the name of the new column to the stored proc param. Does anyone know the syntax for this? Here is the code:

CREATE PROCEDURE procAddNewOptionType

-- new option type parameter

@.OptionName varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert new option type into list of types

INSERTINTO dbo.tblClientOptionList(strOptionName)VALUES(@.OptionName);

-- alter client options table to add new column passed to proc as @.OptionName

ALTERTABLE dbo.tblClientOptions ADD @.OptionName BIT;

RETURN 1

END

Thx,

Jason

Hi jason,

You'd have to execute the ALTER TABLE statements dynamically since your column depends on the OptionName being a variable.

declare @.sql nvarchar(100)

SET @.sql = 'alter table dbo.tblClientOptions add ' + @.optionName + ' BIT'
EXEC sp_executesql @.sql

Although, you'd have to take note possible permissions priviledge given by your DBA(ie. they might revoke ALTER TABLE permission)

hth|||

Thank you very much. That works perfectly!

Jason