动态SQL Select 语句实例

declare @商品1 varchar(50), @单价1 money, @金额1 money
--select @商品1='商品1', @单价1=1000, @金额1=1

declare @商品2 varchar(50), @单价2 money, @金额2 money
select @商品2='商品2', @单价2=2000, @金额2=2

declare @商品3 varchar(50), @单价3 money, @金额3 money
select @商品3='商品3', @单价3=3000, @金额3=3

declare @商品4 varchar(50), @单价4 money, @金额4 money
--select @商品4='商品4', @单价4=4000, @金额4=4

declare @商品5 varchar(50), @单价5 money, @金额5 money
select @商品5='商品5', @单价5=5000, @金额5=5

declare @sql nvarchar(2048)
declare @vindex int, @vc varchar(2) --变量索引号及其文字格式
declare @findex int, @fc varchar(2) --字段索引号及其文字格式



set @sql='select 0 as id'
--set @sql='update table1 '
set @vindex=1
set @findex=1

if @商品1 is not null
begin
set @vc=ltrim(str(@vindex))
set @fc=ltrim(str(@findex))
set @sql=@sql+
',@商品'+@vc+' as 商品'+@fc+
',@单价'+@vc+' as 单价'+@fc+
',@金额'+@vc+' as 金额'+@fc
/*
set @sql=@sql+
'商品'+@fc+'=@商品'+vc+','+
'单价'+@fc+'=@单价'+vc+','+
'金额'+@fc+'=@金额'+vc+','+
*/
set @findex=@vindex+1
end
set @vindex=@vindex+1

if @商品2 is not null
begin
set @vc=ltrim(str(@vindex))
set @fc=ltrim(str(@findex))
set @sql=@sql+
',@商品'+@vc+' as 商品'+@fc+
',@单价'+@vc+' as 单价'+@fc+
',@金额'+@vc+' as 金额'+@fc
set @findex=@findex+1
end
set @vindex=@vindex+1

if @商品3 is not null
begin
set @vc=ltrim(str(@vindex))
set @fc=ltrim(str(@findex))
set @sql=@sql+
',@商品'+@vc+' as 商品'+@fc+
',@单价'+@vc+' as 单价'+@fc+
',@金额'+@vc+' as 金额'+@fc
set @findex=@findex+1
end
set @vindex=@vindex+1

if @商品4 is not null
begin
set @vc=ltrim(str(@vindex))
set @fc=ltrim(str(@findex))
set @sql=@sql+
',@商品'+@vc+' as 商品'+@fc+
',@单价'+@vc+' as 单价'+@fc+
',@金额'+@vc+' as 金额'+@fc
set @findex=@findex+1
end
set @vindex=@vindex+1

if @商品5 is not null
begin
set @vc=ltrim(str(@vindex))
set @fc=ltrim(str(@findex))
set @sql=@sql+
',@商品'+@vc+' as 商品'+@fc+
',@单价'+@vc+' as 单价'+@fc+
',@金额'+@vc+' as 金额'+@fc
set @findex=@findex+1
end
set @vindex=@vindex+1

declare @params nvarchar(2048)
set @params=
'@商品1 varchar(50), @单价1 money, @金额1 money,'+
'@商品2 varchar(50), @单价2 money, @金额2 money,'+
'@商品3 varchar(50), @单价3 money, @金额3 money,'+
'@商品4 varchar(50), @单价4 money, @金额4 money,'+
'@商品5 varchar(50), @单价5 money, @金额5 money'

--set @sql=@sql+' where id=??'
print @sql

exec sp_executesql @sql, @params,
@商品1=@商品1, @单价1=@单价1, @金额1=@金额1,
@商品2=@商品2, @单价2=@单价2, @金额2=@金额2,
@商品3=@商品3, @单价3=@单价3, @金额3=@金额3,
@商品4=@商品4, @单价4=@单价4, @金额4=@金额4,
@商品5=@商品5, @单价5=@单价5, @金额5=@金额5