Dynamic SQL concatenation

Having an issue concatenating the following statement.

Basically I want the length column to add inches after but it will not run. I am going to create a function out of this in the future but unable to get past this step. What gives?

declare @column varchar(255)
declare @sql varchar(5000)
declare @additional varchar(500)

set @column = 'length'

set @additional = 'inches'

select @sql = 'select distinct ps.p_c_id, '

select @sql = @sql +  @column + ' '+@additional+ ' ' + ' as value'

select @sql = @sql

select @sql = @sql + ' from dbo.Product ps
inner join dbo.ProductAttributes psa on ps开发者_如何学Pythona.p_s_id = ps.p_s_id
where ps.p_c_id is not null and ' + @column + ' is not null'


exec (@sql)

---

You are concatenating, what i'm assuming is an int or float value to a string ' inches'...have to cast the "length" value as a varchar... just select your @sql next time to see the resulting syntax and it should jump out at you. here is changes that should work BTW...look at implementing EXEC sp_executesql ...makes dynamic sql less suseptable to injection by using parameters, etc... look up in Books OnLine Sorry...eating Crow...sp_executesql does not protect from injection just improves performance in general...see article MSDN SQL Injection

declare @column varchar(255) 
declare @sql varchar(5000) 
declare @additional varchar(500) 

set @column = 'psa.length' 

set @additional = 'inches' 

select @sql = 'select distinct ps.p_c_id, ' 

select @sql = @sql + 'CAST(' + @column + ' AS varchar(10)) + ' + ''' '+@additional+ ''' ' + ' as value' 

select @sql = @sql 

select @sql = @sql + ' from dbo.Product ps 
inner join dbo.ProductAttributes psa on psa.p_s_id = ps.p_s_id 
where ps.p_c_id is not null and ' + @column + ' is not null' 

--select @sql AS [ExecutableSQL]
exec(@sql)

---

Your output is;

select distinct ps.p_c_id, length inches  as value from dbo.Product ps
inner join dbo.ProductAttributes psa on psa.p_s_id = ps.p_s_id
where ps.p_c_id is not null and length is not null

So it looks like a missing , between length inches assuming you want both;

select @sql = @sql +  @column + ','+ @additional+ ' ' + ' as value'