What's wrong in my dynamic TSQL?

[timscronin]

WHen I execute the statement below against pubs it works correctly

select Name=left(c.name,20),
c.prec, scale = ISNULL(c.scale,0),
t.name from syscolumns c inner join systypes
t on c.xtype = t.xtype where id in
(select id from sysobjects where name = 'titles')
order by c.colid COMPUTE sum(c.prec)

However when I try to change it to use a variable(I will stick this later is a cursor statement) I get an error. Is there something wrong with my attempt at dynamic TSQL?
THanks

declare @sql varchar(300)
declare @tbname varchar(40)
set @tbname = 'titles'
set @sql = 'select Name=left(c.name,20), c.prec,
scale = ISNULL(c.scale,0), t.name from syscolumns
c inner join systypes t on c.xtype = t.xtype '
declare @sql2 varchar(200)
set @sql2= 'where id in (select id from sysobjects where name =' + ltrim(@tbname)+') order by c.colid COMPUTE sum(c.prec)'
exec(@sql + @sql2)

 

[dvrobin]

If you do a print on your sql statements you'll see that the result is:

select Name=left(c.name,20), c.prec,
scale = ISNULL(c.scale,0), t.name from syscolumns
c inner join systypes t on c.xtype = t.xtype where id in (select id from sysobjects where name =titles) order by c.colid COMPUTE sum(c.prec)

notice that there are no ' marks around titles in the subquery. Modify your statement as follows:

declare @sql varchar(300)
declare @tbname varchar(40)
set @tbname = 'titles'
set @sql = 'select Name=left(c.name,20), c.prec,
scale = ISNULL(c.scale,0), t.name from syscolumns
c inner join systypes t on c.xtype = t.xtype '
declare @sql2 varchar(200)
set @sql2= 'where id in (select id from sysobjects where name =' + char(39) + ltrim(@tbname) + char(39) + ') order by c.colid COMPUTE sum(c.prec)'
exec(@sql + @sql2)
Dave Robinder, MCSD

 

[timscronin]

Thanks very much!