/*
使用方法，直接执行，传入参数（series_guid, 查询条件）返回一个数据集
如：
查询该系列cylindrical1下所有产品
dbo.p_getseriesproductdetail 'cylindrical1',''
查询系列cylindrical1下含有bk-1的产品
dbo.p_getseriesproductdetail 'cylindrical1','product_name like ''%bk-1%'''
*/
create procedure p_getseriesproductdetail(@series_guid varchar(40), @condition varchar(1000))
as
declare @paramno nvarchar(5)
declare @sql nvarchar(4000)
set @sql=''
declare p_cursor cursor
local
fast_forward
for select param_no from v_product_params where series_guid=@series_guid
open p_cursor
fetch next from p_cursor into @paramno
while (@@fetch_status = 0)
begin
set @sql = @sql + ',max(case param_no when ' + @paramno + ' then param_value else '''' end) as f' + @paramno + char(13)
fetch next from p_cursor into @paramno
end
close p_cursor
deallocate p_cursor
set @sql='select type_guid, series_guid, product_no, product_name' + @sql + '
from v_product_params where series_guid=''' + @series_guid + ''''
if (ltrim(@condition)'')
set @sql= @sql + ' and ' + @condition
set @sql= @sql + '
group by type_guid, series_guid, product_no, product_name'
print @sql
execute sp_executesql @sql
，