asked on

SQL SP Create Dynamic Columns Exec Add Column Error?

I keep getting this error while trying to run the code below. I don't have any null data...

"The definition for column 'varchar' must include a data type."

Declare @Name	varchar(30)

Fetch Next From @MyCursor
Into  @Name 

exec ('ALTER TABLE dbo.MyTableTest Add ' + @Name + ' varchar(30)')

Open in new window

Jim Horn

Show us the entire T-SQL, including how @MyCursor is populated.

WorknHardr

ASKER

Alter PROCEDURE SP_MyTableTest

AS

BEGIN

Declare @Code		varchar(10)
Declare @Name		varchar(30)
Declare @price		decimal(18, 0)

	SET NOCOUNT ON;

	Declare @MyCursor cursor;

	Set @MyCursor = Cursor For Select Code, Name, Price from tbl_Products 
        Open @MyCursor

	While @@FETCH_STATUS = 0

	Fetch Next From @MyCursor
        Into @Code, @Name, @price	

		Begin
			exec ('ALTER TABLE dbo.MyTableTest Add ' + @Name + ' varchar(30)')	
                End	

        Close @MyCursor
        Deallocate @MyCursor
	
END
GO

Open in new window

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION