1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
|
USE [SR_PROD]
GO
/****** Object: StoredProcedure [dbo].[_My_NumLot] Script Date: 29/11/2021 14:09:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER PROCEDURE [dbo].[_My_NumLot]
(
-- Add the parameters for the function here
@Mag nvarchar(50),@Art nvarchar(50)
)
-- RETURNS NVARCHAR(50)
AS
BEGIN
--
DECLARE @Cpt BIGINT
Declare @Res as nvarchar(50)
declare @varsession varbinary(128)
declare @myValue varchar(10)
declare @mystr as nvarchar(50)=''
declare @car as nvarchar(1)
declare @i as int
declare @lg as int
--lecture de la valeur de compteur dans le contexte
select @myValue=cast(substring(ci,1,10) as varchar(10))
from (select ci=context_info()) t
--Verification de la valeur et gestion du NULL
print concat('contexte = ', @myValue)
set @myValue=isnull(@myValue,'0')
-- Nettoyage de la chaine
set @lg=len(@myValue)
set @i=1
set @mystr='0'
while (@i<=@lg) begin
--
set @car=SUBSTRING( @myValue,@i,1)
if @car in ('0','1','2','3','4','5','6','7','8','9') set @mystr=@mystr+@car
--
set @i=@i+1
--
end/*while*/
-- incrementation de la valeur
set @Cpt=isnull(cast(@mystr as bigint),0)+1
print concat('compteur= ',@Cpt)
--Sauvegarde du contexte
set @myValue=cast(@Cpt as varchar(10))
--set @myValue=concat('T',@Cpt)
set @varsession=cast(@myvalue as binary(10))
set context_info @varsession
--
-- select Name=cast(substring(ci,1,10) as varchar(10)) from (select ci=context_info()) t
-- set @cpt=10
-- Generation de la chaine
SELECT @Res=
case when @art='1' then
CONCAT(@mag, REPLICATE('0',6-LEN(COUNT(*)+@Cpt)),COUNT(*)+@Cpt)
when @art='FDT-PRT002' then
CONCAT('PRET', REPLICATE('0',6-LEN(COUNT(*)+@Cpt)),COUNT(*)+@Cpt)
when @art='C-SEC' then
CONCAT(@Mag, REPLICATE('0',6-LEN(COUNT(*)+@Cpt)),COUNT(*)+@Cpt)
when @art='FDT-PRT001' then
CONCAT(@Mag, REPLICATE('0',6-LEN(COUNT(*)+@Cpt)),COUNT(*)+@Cpt)
else '' end
FROM OBTN WHERE ItemCode= @art
-- Return the result of the function
--RETURN @res
select @Res as 'Result'
END
GO |
Partager