alter proc proc_searchPrice ( @productname varchar(50) ) as begin declare @productname1 nvarchar(50)
declare cur_dupro cursor for select distinct top 20 product_name from t_Price_History where product_name like '%'+@productname+'%'
open cur_dupro fetch next from cur_dupro into @productname1 while (@@fetch_status=0) begin select distinct product_name, (select top 1 price from t_Price_History where period='1999' and product_name=''+@productname1+'') as '1999', (select top 1 price from t_Price_History where period='2000' and product_name=''+@productname1+'') as '2000', (select top 1 price from t_Price_History where period='2001' and product_name=''+@productname1+'') as '2001', (select top 1 price from t_Price_History where period='2002' and product_name=''+@productname1+'') as '2002', (select top 1 price from t_Price_History where period='2003' and product_name=''+@productname1+'') as '2003', (select top 1 price from t_Price_History where period='2004' and product_name=''+@productname1+'') as '2004' from t_Price_History where product_name =''+@productname1+'' fetch next from cur_dupro into @productname1 end close cur_dupro end