create Procedure sp_getpricelist(@m_strPID varchar(1000))asbegin --region 创建临时表 IF object_id('tempdb..##ThisIsATest') is not null begin drop table ##ThisIsATest end CREATE TABLE ##ThisIsATest ( ID int Primary Key, --经销商报价产品ID PID bigint, --产品ID Province nvarchar(100) default(''), --省份 City nvarchar(100) default(''), --城市 Price money default(0.00) --价格 --其它字段忽略 )
--endregion 创建临时表结束 declare @m_lPriceId bigint
declare @SplitArray table(ID bigint Primary Key) declare @tempIndex int set @tempIndex=1 if charindex(',',@m_strPID)=0 begin insert into @SplitArray values(Convert(bigint,@m_strPID)) set @tempIndex=0 end while @tempIndex>0 begin set @tempIndex=charindex(',',@m_strPID) print @tempIndex if @tempIndex>0 begin insert into @SplitArray values(Convert(bigint,substring(@m_strPID,1,@tempIndex-1))) set @m_strPID=substring(@m_strPID,@tempIndex+1,datalength(@m_strPID)-@tempIndex) if charindex(',',@m_strPID)=0 begin insert into @SplitArray values(Convert(bigint,@m_strPID)) end end end --select * from @SplitArray declare p_cursor1 cursor for select ID from @SplitArray open p_cursor1 fetch next from p_cursor1 into @m_lPriceId declare @loopi1 int set @loopi1=1 --设置计数器 while @@FETCH_STATUS = 0 begin declare @m_pCursorId int --循环指针 declare @strsql nvarchar(4000)
declare @ParamName varchar(100) declare @PValue nvarchar(4000)
declare @Province nvarchar(100) declare @City nvarchar(100) declare @Price money declare @PID bigint insert into ##ThisIsATest(ID) values(@m_lPriceId) declare p_cursor cursor for select B.ID from T_Product_PriceInfo A,T_Product_Param_Value B,T_Cate_Param C,T_Product_Quote D where D.ID=@m_lPriceId and A.ID=B.P_ID and B.C_ID=C.ID and A.ID=D.P_ID open p_cursor fetch next from p_cursor into @m_pCursorId declare @loopi int set @loopi=1 --设置计数器 while @@FETCH_STATUS = 0 begin select @PID=A.ID, @ParamName=C.ParamName,@Province=D.Province,@City=D.City,@Price=D.Price, @PValue = case when (C.ValueType>0 and C.ValueType<11) then Convert(nvarchar(4000),B.S_Value) when (C.ValueType>10 and C.ValueType<21) then Convert(nvarchar(4000),B.I_Value) when (C.ValueType>20 and C.ValueType<31) then Convert(nvarchar(4000),B.D_Value) end from T_Product_PriceInfo A,T_Product_Param_Value B,T_Cate_Param C,T_Product_Quote D where D.ID=@m_lPriceId and A.ID=D.P_ID and A.ID=B.P_ID and B.C_ID=C.ID and B.ID=@m_pCursorId if col_length('tempdb..##ThisIsATest',@ParamName) is null begin set @strsql='Alter table ##ThisIsATest add '+@ParamName+' nvarchar(4000)' Exec(@strsql) end set @strsql ='update ##ThisIsATest set '+@ParamName+'='''+@PValue+''',' set @strsql=@strsql+' PID='+Convert(nvarchar(10),@PID)+',' set @strsql=@strsql+' Province='''+@Province+''',' set @strsql=@strsql+' City='''+@City+''',' set @strsql=@strsql+' Price=convert(money,'''+Convert(nvarchar(10),@Price)+''')' --其它字段忽略 set @strsql=@strsql+' where ID='+Convert(nvarchar(10),@m_lPriceId) Exec(@strsql) set @loopi= @loopi+1 fetch next from p_cursor into @m_pCursorId end close p_cursor DEALLOCATE p_cursor set @loopi1= @loopi1+1 fetch next from p_cursor1 into @m_lPriceId end close p_cursor1 DEALLOCATE p_cursor1 delete from ##ThisIsATest where PID is null set @strsql='select * from ##ThisIsATest' Exec(@strsql) drop table ##ThisIsATest --go --删除临时表endgo
sp_getpricelist '1,2,3,4,5,6,7'
