set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[SHT_BTN_GOODSz_PROP]
(
@UserID int,
@OpCode int,
@Items varchar(2000),
@Root varchar(200)='GRIDDATA/ITEM'
)
as
begin
declare @hdoc int
declare @ret int
declare @retmsg varchar(1000)
exec @ret=sp_xml_preparedocument @hdoc output,@Items
if @ret<>0
begin
set nocount on
set @ret=-4354
set @retmsg='解析xml文件失败'
goto outcode
end
select *
into #VItems
from openxml(@hdoc,@Root,1)
with
(
DATAKEY INT,
PAGEKEY int,
PROPNAME VARCHAR(50),
PROPVALUE INT,
GPROPID int,
DATAID int
)
对于页面所传的值，或者
DATAKEY INT,
PAGEKEY int,
PROPNAME VARCHAR(50),
PROPVALUE INT,
GPROPID int,
DATAID int
分别传入什么值都可以在跟踪器中查看
SHT_BTN_GOODSz_PROP 1, 1,'<?xml version="1.0" encoding="gb2312" ?><GRIDDATA><ITEM DATAKEY=""
PROPNAME="1" PROPVALUE="2" PAGEKEY="100425" /></GRIDDATA>'
if @@rowcount<=0
begin
set nocount on
set @ret=-3432
set @retmsg='没有可处理的信息'
goto outcode
end
if @OpCode in(1,2,3)
begin
if exists(select * from #VItems where PAGEKEY=null or PAGEKEY='')
begin
set @ret=-3423
set @retmsg='没有相应商品信息'
goto outcode
end
end
if @OpCode=1
begin
if exists( select * from DPV_GOODSPROPERTIES a,#VItems c
where
a.GPropID=c.PROPNAME and a.GoodsID=c.PAGEKEY) --这个PAGEKEY弄个好长时间 以后用跟踪器查找
begin
set @ret = -3312
set @retmsg = '商品属性重复'
goto outcode
end
end
begin tran
if @OpCode=1
begin
if(exists(select * from #VItems where DATAKEY=0))
begin
-- declare @mid int
-- select @mid=max(GoodsID) from DPV_GOODSPROPERTIES where 1=1
--
-- select identity(int,1,1) as rid,*
-- into #TItems
-- from #VItems
-- where DATAKEY=0
insert DPV_GOODSPROPERTIES(GoodsID,GPropID,PropValue)
select PAGEKEY,PROPNAME,PROPVALUE from #VItems
if @@error<>0
begin
rollback tran
set @ret = -3312
set @retmsg = '插入信息失败'
goto outcode
end
end
end
if @OpCode=2
begin
if exists(select * from #VItems where DATAKEY<>0)
begin
update a
set
PropValue=b.PROPVALUE
from DPV_GOODSPROPERTIES a inner join #VItems b
on a.GoodsID=b.DATAKEY and a.GPropID=b.PROPNAME
if @@error<>0
begin
rollback tran
set @ret=-3422
set @retmsg='修改失败'
goto outcode
end
end
end
if @OpCode=3
begin
if exists(select * from #VItems where DATAKEY<>0)
begin
delete from DPV_GOODSPROPERTIES
where DPV_GOODSPROPERTIES.GoodsID=(select DATAKEY from #VItems)
if @@error<>0
begin
rollback tran
set @ret=-3422
set @retmsg='删除失败'
goto outcode
end
end
end
commit tran
set @ret = @userid
set @retmsg = '操作成功'
outcode:
exec sp_xml_removedocument @hdoc
select @ret, @retmsg
return @ret
end
