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 89 90 91
|
CREATE PROCEDURE dbo.P_GetNewNumberD(
@newNumber varchar(8) OUTPUT
)
AS
BEGIN
DECLARE @resTable TABLE(number VARCHAR(8))
DECLARE @root VARCHAR(8)
DECLARE @left INT
DECLARE @middle VARCHAR(2)
DECLARE @right VARCHAR(2)
DECLARE @right1 INT
DECLARE @right2 INT
DECLARE @middle1 INT
DECLARE @middle2 INT
SET @root = (SELECT param_value FROM parameter
WHERE param_code = 'newLastNumber')
SET @left = SUBSTRING(@root,1,3)
SET @middle = SUBSTRING(@root,4,2)
SET @middle1 = ASCII(@middle)
SET @middle2 = ASCII(SUBSTRING(@middle,2,1))
SET @right = SUBSTRING(@root,6,2)
SET @right1 = SUBSTRING(@root,6,1)
SET @right2 = SUBSTRING(@root,7,1)
------------------------------------------------
-- Increment Right Part of the number
------------------------------------------------
IF @right != 99
BEGIN
IF CONVERT(INT,@right1) = 0
BEGIN
IF CONVERT(INT,@right2) != 9
SET @right = '0' + CONVERT(VARCHAR(1),@right + 1)
ELSE
SET @right = @right + 1
END
ELSE
BEGIN
SET @right = @right + 1
END
END
--------------------------------------------------
-- Increment middle, alphanumeric part of the number
--------------------------------------------------
ELSE IF @middle1 + @middle2 != 180
BEGIN
IF @middle2 != 90
SET @middle2 = @middle2 + 1
ELSE
BEGIN
SET @middle1 = @middle1 + 1
SET @middle2 = 65
END
SET @right = '00'
END
--------------------------------------------------
-- Increment left part of the number
--------------------------------------------------
ELSE
BEGIN
SET @left = @left + 1
SET @middle1 = 65
SET @middle2 = 65
SET @right = '00'
END
SET @middle = CHAR(@middle1) + CHAR(@middle2)
SET @root = CONVERT(VARCHAR(3),@left) + @middle + CONVERT(VARCHAR(2),@right)
--------------------------------------------------
-- Update last number value
--------------------------------------------------
UPDATE parameter
SET param_value = @root
OUTPUT INSERTED.param_value INTO @resTable
WHERE param_code = 'newLastNUmberD'
--------------------------------------------------
-- Select from the output table
--------------------------------------------------
SELECT @new_number = number FROM @resTable
RETURN 0
END |
Partager