/*
Run the command directly. input the parameter (series_guid, query condition) to return a dataset.
For example:
Query all products of Cylindrical1.
Dbo. P_GetSeriesProductDetail 'cylindrical1 ',''
Query products with BK-1 under Cylindrical1
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