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

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+''','
se

t @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
--删除临时表 /
end
go

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