动态SQL连接

Question

连接以下语句时出现问题。

基本上，我希望长度列增加英寸后，但它不会运行。我将在将来创建一个函数，但无法跳过这一步。怎么回事？

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 psa.p_s_id = ps.p_s_id
where ps.p_c_id is not null and ' + @column + ' is not null'


exec (@sql)

Answer 1

您正在连接，我假设是一个整型或浮点型的值到一个字符串‘英寸’...必须将"length“值转换为varchar...下一次只需选择@sql来查看生成的语法，它就会跳出来。以下是在实现EXEC sp_executesql ...makes动态BTW...look时应该做的更改，通过使用参数等，不太容易注入。在general...see文章MSDN SQL Injection中的书籍中查找Sorry...eating Crow...sp_executesql does not protect from just improves performance

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)

Answer 2

你的输出是；

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

所以它看起来像是length inches之间缺少的,，假设你想要这两个；

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