Alter Table Column Syntax Error

Question

I needed to create a table with dynamic columns, so I created a cursor that loops through the records of a table and will create the necessary columns, however, is giving me this error:

Incorrect syntax near 'INT'.

Example code:

SELECT @sql = 'ALTER TABLE #temp3 ADD ' + @nome + ' INT' EXEC (@sql);

I have also tried this:

EXEC ('ALTER TABLE #temp3 ADD ' + @nome + ' INT')

But still the same error

Any suggestions?

Edit: Examples of values that can receive @nome

Very Bad
Bad
Good
Very Good

Without knowing what @nome contains, it's anyones guess. As well as/instead of EXECing the constructed string, try PRINTing it as well, so you can see what's being run. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 10, 2013 at 10:35
And my other recommendation would be: try harder to come up with a solution that doesn't need dynamic columns - maybe it's just a presentation issue that can be better dealt with at a non-database layer. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 10, 2013 at 10:37
can't fin the reference but the exec execution environment does not know about temporary table. — tschmit
– tschmit, Commented Jan 10, 2013 at 10:45
@tschmit007 - not true. Within the scope of the exec, you can reference temp tables created in the outer scope. Maybe you're thinking of table variables — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 10, 2013 at 10:48

Damien_The_Unbeliever · Accepted Answer · 2013-01-10 10:52:39Z

You've indicated that @nome may contain, for instance, Very Bad. If that is so, it contains a space - you need to delimit the name so that SQL Server knows that the space is part of the name:

SELECT @sql = 'ALTER TABLE #temp3 ADD [' + @nome + '] INT' EXEC (@sql);

or more properly, use QUOTENAME

SELECT @sql = 'ALTER TABLE #temp3 ADD ' + QUOTENAME(@nome) + ' INT' EXEC (@sql);

Otherwise, SQL Server is trying to add a column called Very with a datatype of Bad, and it doesn't even know how to interpret int after that.

Apparently the problem is solved. But I'll do some tests. Thank you!

tschmit · Accepted Answer · 2013-01-10 10:59:01Z

thanks to @Damien that encourage me to investigate:

if object_id(N'#tempg') is not null drop table #tempg select 1 as i into #tempg select * from #tempg alter table #tempg add j int exec sp_executesql N'alter table #tempg add k int' select * from #tempg

note that

exec 'alter table #tempg add l int'

fails with

Msg 102, Niveau 15, État 1, Ligne 1 Syntaxe incorrecte vers 'alter table #tempg add l int'.

============================================= Edition

exec ('alter table #tempg add l int')

runs

That only produces an error because to exec a literal string, you have to use EXEC() with () bracketing the string. exec object works for executing stored procedures.

Collectives™ on Stack Overflow

Alter Table Column Syntax Error

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related