Altering a table using Dynamic Query

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

wayneburrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Hello wayneburr,


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


Regards,

Aneesh
0
pssandhuCommented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
exec ('ALTER TABLE #t ADD  [T'+ @rtemps+'] VARCHAR(10) NULL')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

wayneburrAuthor Commented:
A billion points to you!
I needed to keep the '-' so this works exactly what I needed.
Thanks!
0
wayneburrAuthor Commented:
pssandhu;

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

Thanks again.
0
pssandhuCommented:
oh okay.. No worries! Cheers!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.