关于存储过程

create proc InsertIntoSXAndSXZ  --同时向属性表和属性值表插入数据
(
 @SXMingChen  varchar(100),
 @FLID int,
 @SXSFKX int,
 @LuRuFangShi int,
 @AddManID int,
 @AddTime datetime,
 @IsDelete int,
 @IsJiansuo int,
 @SXZMingCheng varchar(100),
 @FenGeFu varchar(50)
 )
as
begin transaction
 declare @count int
 declare @SXID int
declare @next int
set @next=1
insert into  MLSCShuXing (SXMingChen,FLID,SXSFKX,LuRuFangShi,AddManID,AddTime,IsDelete,IsJiansuo)values(@SXMingChen,@FLID,@SXSFKX,@LuRuFangShi,@AddManID,@AddTime,@IsDelete,@IsJiansuo) select @SXID=SCOPE_IDENTITY()
declare @GeSu int
select @GeSu= dbo.Get_StrArrayLength(@SXZMingCheng,@FenGeFu)
while @next<=@GeSu
begin
insert into MLSCShuXingZhi (ShuXingID,SXZMingCheng,AddManID,AddTime,IsDelete)values(@SXID, dbo.Get_StrArrayStrOfIndex(@SXZMingCheng,@FenGeFu,@next),@AddManID,@AddTime,@IsDelete )
set @next=@next+1
end

select @count=@@error
if(@count=0)
commit transaction
else  
rollback transaction






create proc InsertIntoProductAndProductSKU  --同时向产品表和产品SKU表插入数据
(
 @PBianHao  varchar(100),
 @PMingCheng varchar(100),
 @PMiaoShu varchar(100),
 @PXiangXi text,
 @PPinPaiID int,
 @PPinPaiName varchar(100),
 @PShenHe int,
 @AddManID int,
 @AddTime datetime,
 @IsDelete int,
 @MinLongJia decimal(18,2),
 @DangQianJia decimal(18,2),
 @ZheKou decimal(18,1),
 @XiaoLiang int,
 @ShagnJiaID int,
 @KuCunLiang int,
 @KuCunJingGaoLiang int,
 @GuanJianZi varchar(200),
 @JiaRuTuiJian int,
 @ShiFouShangJia int,--以上是Product表字段
 
 @ShiChangJia  decimal(18,2), --市场价
 @SXID varchar(200),--属性ID
 

 )
as
begin transaction
 declare @count int --记录错误
 declare @PID int --产品ID
    begin
      insert into MLSCProduct(PBianHao,PMingCheng,PMiaoShu,PXiangXi,PPinPaiID,PPinPaiName,PShenHe, AddManID,AddTime,IsDelete,MinLongJia,DangQianJia,ZheKou,XiaoLiang,ShagnJiaID,KuCunLiang,KuCunJingGaoLiang,GuanJianZi,JiaRuTuiJian,ShiFouShangJia)
         values(@PBianHao,@PMingCheng,@PMiaoShu,@PXiangXi,@PPinPaiID,@PPinPaiName,@PShenHe,@AddManID,@AddTime,@IsDelete,@MinLongJia,@DangQianJia,@ZheKou,@XiaoLiang,@ShagnJiaID,@KuCunLiang,@KuCunJingGaoLiang,@GuanJianZi,@JiaRuTuiJian,@ShiFouShangJia)
         select @PID=SCOPE_IDENTITY()
      insert into MLSCProductSKU (PID,ShiChangJia,SXID,AddManID,AddTime,IsDelete)values(@PID,@ShiChangJia,@SXID,@AddManID,@AddTime,@IsDelete)
    end
select @count=@@error
if(@count=0)
commit transaction
else  
rollback transaction

 

俩函数：

Create function [dbo].[Get_StrArrayLength]
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int

set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
   set @start=@location+1
   set @location=charindex(@split,@str,@start)
   set @length=@length+1
end
return  @length
end

 

create function [dbo].[Get_StrArrayStrOfIndex]
(
@str varchar(1024), --要分割的字符串
@split varchar(10), --分隔符号
@index int --取第几个元素
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int

set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)

set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
   set @start=@location+@seed
   set @location=charindex(@split,@str,@start)
   set @next=@next+1
end

if @location=0 select @location=len(@str)+1
return substring(@str,@start,@location-@start)
end