asked on

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

Aneesh

Hello wayneburr,

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

Regards,

Aneesh

pssandhu

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

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wayne Burr

ASKER

A billion points to you!
I needed to keep the '-' so this works exactly what I needed.
Thanks!

Wayne Burr

ASKER

pssandhu;

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

Thanks again.

pssandhu

oh okay.. No worries! Cheers!