Solved

Altering a table using Dynamic Query

Posted on 2009-06-28
6
153 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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