Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL SP Create Dynamic Columns Exec Add Column Error?

Posted on 2014-01-22
6
Medium Priority
?
294 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 66

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 66

Accepted Solution

by:
Jim Horn earned 400 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 12

Assisted Solution

by:Henk van Achterberg
Henk van Achterberg earned 400 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

721 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