对于增删改的存储过程明白很多啊-优快云博客

本文介绍了一个SQL Server中的存储过程，该过程用于处理XML格式的商品属性数据，并实现插入、更新或删除这些属性的功能。文章详细展示了如何使用T-SQL进行XML数据解析及与数据库表的操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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