SQL Server Performance Forum – Threads Archive

Finding syntax error

Hi All, Why this syntax giving such a error ?? Declare @sql varchar(100)
Declare @t1 varchar(4)
select @t1 = object_id(‘tablename’)
select @sql = ‘select name from syscolumns where id = ‘
select @sql = @sql + @t1
EXEC (@sql)
Error Message :-
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘*’. Thanks
ASP

The object_id returns int, therefore your @t1 variable should be declared as int and not varchar(4). Jon M

Not correct!
INT has a higher precedence than VARCHAR, so SQL Server will implicitely convert to INT. Try explicitely [email protected] to VARCHAR. But I don’t see a * in your batch. Is this the complete statement or only a part?
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Object_id is above 9999, so @t1 is set to ‘*’.

Doh, thanks for catching that, Mirko! —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Not correct! INT has a higher precedence than VARCHAR…<hr height="1" noshade id="quote"></blockquote id="quote"> Maybe FrankKalis has a better way of doing it. But this is what I mean: Declare @sql varchar(100) Declare @t1 int –>change your variable type declaration select @t1 = object_id(‘tablename’) select @sql = ‘select name from syscolumns where id = ‘ select @sql = @sql + str(@t1) EXEC (@sql) Jon M Please ignore this message, I got what you mean FrankKalis [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Jon M

Thanks everybody for quick reply to this post. Actually there was very trivial mistake i was making. Size of varchar data type was problem. I did this… Declare @sql varchar(100) Declare @t1 varchar(20)–Size changed from 4 to 20.. select @t1 = object_id(‘tablename’) select @sql = ‘select name from syscolumns where id = ‘ select @sql = @sql + @t1 EXEC (@sql) varchar(4) was little less to hold the result of object_id results.. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> This worked fine… Again thanks to everybody..

Why Dynamic SQL?
Isnt this enough? Declare @table_name varchar(50)
set @table_name =’yourTable’
Select Column_name from information_Schema.columns where table_name = @table_name
Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Jon M select @sql = @sql + str(@t1) <hr height="1" noshade id="quote"></blockquote id="quote"> For a lazycoder this is brilliant. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Another one would be <pre id="code"> select @sql = @sql + LTRIM(@t1) </pre id="code"> However, somehow I think this <pre id="code"> select @sql = @sql + CAST(@t1 AS VARCHAR(10)) </pre id="code"> is a tick faster. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

]]>