Get WMS Static GoodLocation By Dynamic SQL

Dynamic SQL Store Procedure:
Note:
use variable,you need convert varchar and as a variable,not directly use variable,like this: '+convert(varchar(10),@init_column)+'    '+@the_column_name+'
create procedure sp_getgoodlocationviaplatoon
    @the_platoon int
as
begin
declare @the_column_name varchar(
32) declare @sql varchar(8000) declare @init_column int declare @the_column int begin select @the_column=max(F_column) from Sys_GoodLocation select @sql = 'select F_Floor, ' set @init_column=1 set @the_column_name=concat('[',@init_column,']') while @init_column<@the_column begin set @sql= @sql + 'sum(case F_Column when '+convert(varchar(10),@init_column)+' then F_GoodLocationNum else 0 end) as '+@the_column_name+',' --print @sql set @init_column=@init_column+1 set @the_column_name=concat('[',@init_column,']') end if @init_column=@the_column begin set @sql= @sql + 'sum(case F_Column when '+convert(varchar(10),@init_column)+' then F_GoodLocationNum else 0 end) as '+@the_column_name+'' end select @sql= @sql + ' from Sys_GoodLocation where F_Platoon='+convert(varchar(10),@the_platoon)+' group by F_Floor order by F_Floor' exec(@sql) end end
go

Call:

exec sp_getgoodlocationviaplatoon 1
exec sp_getgoodlocationviaplatoon 2
exec sp_getgoodlocationviaplatoon 3

Result:

posted @ 2019-04-22 19:51  AriLee  Views(167)  Comments(0Edit  收藏  举报