Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

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

0
wayneburr
Asked:
wayneburr
  • 2
  • 2
  • 2
1 Solution
 
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now