Solved

Altering a table using Dynamic Query

Posted on 2009-06-28
6
152 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
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.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help creating a stored procedure 4 63
Help with SQL joins 9 53
Truncate vs Delete 63 107
Why is the output of this function is like this? 4 38
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

789 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