Solved

SQL SP Create Dynamic Columns Exec Add Column Error?

Posted on 2014-01-22
6
293 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 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

617 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