mssql 关于传入文本字符进行拆分并插入到临时表中,以供使用,以下只是一个例子,供参考。
create procedure GetVIPMark(@SLDAT datetime, @PNO integer, @SNO integer, @VIPNO varchar(20), @PLUInfo text , @CurVIPMark varchar(50) output , @TotalVIPMark varchar(150) output, @Return bit output)
as
begin
/*
*/
declare @UID bigint
declare @CharTable table (pluno varchar(50), qty NUMERIC(9,3), mark numeric(12,4))
declare @TMP varchar(50)
declare @B sql_variant, @C sql_variant
declare @LEN int, --字符串长度
@B_POS int, --开始位置
@E_POS int, --结束位置
@C_POS int, --当前位置
@F_POS int,
@G_POS int,
@summark numeric(12,4),
@TMP_LEN int,@i int
declare @PLU varchar(50), @MARK varchar(50), @QTY varchar(50)
select @Return = 0
select @CurVIPMark='0'
select @TotalVIPMark='0'
--将传入的文本信息写入临时表,记录之间通过';'进行分割,商品编码,数量,销售金额通过','进行分割。
select @LEN = datalength(@PLUInfo), @B_POS = 1, @E_POS = 0
while @B_POS <= @LEN
begin
set @G_POS = 0
set @C_POS = patindex('%;%', substring(@PLUInfo, @B_POS, @LEN))
set @E_POS = case @C_POS when 0 then @LEN + 1 else @E_POS + @C_POS end
set @TMP_LEN = len(substring(@PLUInfo, @B_POS , @E_POS - @B_POS ))
set @F_POS = patindex('%,%', substring(substring(@PLUInfo, @B_POS , @E_POS - @B_POS ), 1, @TMP_LEN))
set @G_POS = case @F_POS when 0 then @TMP_LEN + 1 else @G_POS + @F_POS end
select @PLU = substring(substring(@PLUInfo, @B_POS , @E_POS - @B_POS ), 1 , @G_POS - 1 )
select @Mark = substring(substring(@PLUInfo, @B_POS , @E_POS - @B_POS ), @G_POS + 1, @TMP_LEN)
select @TMP = convert(varchar(50), @Mark)
select @i = patindex('%,%', @TMP)
select @QTY = substring(@TMP, 1, @i - 1)
select @Mark = substring(@TMP, @i + 1, @TMP_LEN)
if (isnull(@Mark , '') = '') select @Mark = '0'
if (isnull(@QTY , '') = '') select @QTY = '0'
insert into @CharTable( pluno , qty , mark)
values(Rtrim(LTRIM(@PLU)), convert(varchar(50), convert(numeric(12,2), @QTY)), convert(varchar(50), convert(numeric(12,2), @Mark)))
set @B_POS = @E_POS + 1
end
--商品货号没有 直接返回
if not exists(select 1 from @chartable a join basplumain b on a.pluno=b.pluno)
begin
select @CurVIPMark = 0
select @TotalVIPMark = 0
return
end
select @UID = dbo.GetUID(@SLDAT, @PNO, @SNO)
if @@error > 0
begin
select @CurVIPMark = 0
select @TotalVIPMark = 0
return
end
SELECT @CurVIPMark = SUM(MARK)
FROM (
SELECT C.VIPRAT, CASE WHEN SUM(B.MARK)>=0 THEN (FLOOR((SUM(B.MARK)*10.00)/C.VIPRAT))/10.00
ELSE (CEILING((SUM(B.MARK)*10.00)/C.VIPRAT))/10.00 END AS MARK
from @CharTable B,BASDEPT C (nolock) ,BASPLUMAIN D (nolock) ,BASPLUPRC E (nolock)
WHERE B.PLUNO = D.PLUNO AND D.DPTID = C.DPTID AND D.PLUID = E.PLUID AND ISNULL(E.ASLPRC,0) = 0
and D.PLUID NOT IN (SELECT PLUID
FROM dbo.VWMMXPLU
WHERE BTIME<=LEFT(CONVERT(CHAR(5),GETDATE(),108),2)+RIGHT(CONVERT(CHAR(5),GETDATE(),108),2)
AND ETIME>=LEFT(CONVERT(CHAR(5),GETDATE(),108),2)+RIGHT(CONVERT(CHAR(5),GETDATE(),108),2))
AND D.PLUID NOT IN(SELECT PLUID FROM DOC70PLU WHERE (TYPE =2 OR TYPE = 4) and ready = 1 )
AND C.VIPRAT > 0
GROUP BY C.VIPRAT
) AA
select @CurVIPMark = isnull(@CurVIPMark,0)
select @TotalVIPMark = rest FROM BASVIP WHERE VIPNO = @VIPNO
select @Return = 1
select 1
end