Solved

SQL SP Create Dynamic Columns Exec Add Column Error?

Posted on 2014-01-22
6
279 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
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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:ScottPletcher
ScottPletcher 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

937 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

Need Help in Real-Time?

Connect with top rated Experts

1 Experts available now in Live!

Get 1:1 Help Now