Solved

Altering a table using Dynamic Query

Posted on 2009-06-28
6
148 Views
Last Modified: 2012-05-07
I'm using a Dynamic Query to alter a temp table in Stored Procedure.  I'm using Values to name the columns from a Cursor.
My problem is that the values are numbers and will contain negivitve numbers.  ex:-55, -125 etc.
When the table is altered with the column name that has a "-",
I get a syntax error: "Incorrect syntax near '-'."
I have casted the value to a string and even added a "t" in front of the value, but still get the error.

Any help would be great.
Thanks!
...

--@rtemps = -55

BEGIN 

	DECLARE tempvalue_cursor CURSOR FOR

	SELECT R1 From dbo.LVWAREQUALIMPORT

	SET @rtemps = CAST(@rtemps AS VARCHAR(10))

	exec ('ALTER TABLE #t ADD T'+ @rtemps+' VARCHAR(10) NULL')

...

Open in new window

0
Comment
Question by:wayneburr
  • 2
  • 2
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24733314
Hello wayneburr,


exec ('ALTER TABLE #t ADD  ['+ @rtemps+'] VARCHAR(10) NULL')


Regards,

Aneesh
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24733323
Use the Absolute function ABS() to get rid the of the negative in front of the number. Something like this:
 
 

...

--@rtemps = -55

BEGIN 

	DECLARE tempvalue_cursor CURSOR FOR

	SELECT R1 From dbo.LVWAREQUALIMPORT

	SET @rtemps = CAST(ABS(@rtemps) AS VARCHAR(10))

	exec ('ALTER TABLE #t ADD T'+ @rtemps+' VARCHAR(10) NULL')

...

Open in new window

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24733328
exec ('ALTER TABLE #t ADD  [T'+ @rtemps+'] VARCHAR(10) NULL')
0
Zoho SalesIQ

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

 

Author Closing Comment

by:wayneburr
ID: 31597775
A billion points to you!
I needed to keep the '-' so this works exactly what I needed.
Thanks!
0
 

Author Comment

by:wayneburr
ID: 24733392
pssandhu;

Thanks for the response!  The ABS would/did work, but I needed to keep the '-' sign.

Thanks again.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24733411
oh okay.. No worries! Cheers!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

17 Experts available now in Live!

Get 1:1 Help Now