Solved

SQL SP Create Dynamic Columns Exec Add Column Error?

Posted on 2014-01-22
6
289 Views
Last Modified: 2014-01-22
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
Comment
Question by:WorknHardr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39800755
Show us the entire T-SQL, including how @MyCursor is populated.
0
 

Author Comment

by:WorknHardr
ID: 39800830
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 100 total points
ID: 39800880
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 12

Assisted Solution

by:Henk van Achterberg
Henk van Achterberg earned 100 total points
ID: 39800985
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 39801049
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
 

Author Closing Comment

by:WorknHardr
ID: 39801326
Cool, thanks for ALL your help...
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question