Sql存储过程中临时表和游标的使用

create Procedure sp_getpricelist
(
@m_strPID varchar(1000)
)
as
begin
 --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
 --删除临时表 http://www.shenjk.com/
end
go

sp_getpricelist '1,2,3,4,5,6,7'