• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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

0
WorknHardr
Asked:
WorknHardr
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Show us the entire T-SQL, including how @MyCursor is populated.
0
 
WorknHardrAuthor Commented:
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

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Looks correct, but a couple of thoughts...

1 I'd add '.. WHERE Name IS NOT NULL' to the end of the cursor declaration, just to make sure.

Set @MyCursor = Cursor For Select Code, Name, Price from tbl_Products WHERE Name IS NOT NULL

2 I'd also add square brackets to the column name, just in case the value in name has a space or character that can't be used as a column name.

exec ('ALTER TABLE dbo.MyTableTest Add [' + @Name + '] varchar(30)')
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Henk van AchterbergSr. Technical ConsultantCommented:
Try this code:

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

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

	While @@FETCH_STATUS = 0

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

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


        Close @MyCursor
        Deallocate @MyCursor

Open in new window

0
 
Scott PletcherSenior DBACommented:
In your initial code, only the Fetch was in the While loop, since there was no BEGIN after the While.

Since I don't like having to code the same Fetch twice for every cursor, I use this approach:

Open @MyCursor

While 1 = 1
Begin
      Fetch Next From @MyCursor
      Into @Code, @Name, @price      
      If @@FETCH_STATUS <> 0
          Break
      If @Name > ''
          Exec ('ALTER TABLE dbo.MyTableTest Add [' + @Name + '] varchar(30)')      
End --While

Close @MyCursor
Deallocate @MyCursor
0
 
WorknHardrAuthor Commented:
Cool, thanks for ALL your help...
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now