/*
Use method, execute directly, pass in parameter (series_guid, query condition) return a dataset
Such as:
Query all products under the CYLINDRICAL1 series
Dbo. P_getseriesproductdetail ' Cylindrical1 ', '
Query series Cylindrical1 products containing BK-1
Dbo. P_getseriesproductdetail ' Cylindrical1 ', ' product_name like '%bk-1% '
*/
CREATE PROCEDURE p_getseriesproductdetail (@series_guid varchar), @condition varchar (1000))
As
DECLARE @ParamNo nvarchar (5)
DECLARE @SQL nvarchar (4000)
Set @SQL = '
DECLARE p_cursor Cursor
Local
Fast_forward
For the 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 ') 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