问在sql过程中返回值为空。

EN

ALTER PROCEDURE [dbo].[_MG_NextInventoryPackNumberForPartNumber]
@sPartNumber NVARCHAR(254)  --##PARAM @sPartNumber The part number for which we need to get the new InventoryPackNo
AS 
BEGIN

SET NOCOUNT ON;

DECLARE @num INT,
    @sNewInventoryPackNum NVARCHAR(254),
    @StringNum NVARCHAR(254)
SET @StringNum = (SELECT    SUBSTRING(InventoryPackNo, 9, 5) AS pName
                  FROM      InventoryPack
                  WHERE     SUBSTRING(InventoryPackNo, 1, 8) LIKE @sPartNumber)
SET @num = CONVERT(INT, @StringNum)
SET @num = @num + 1
IF @num >= 1 AND @num <= 9 
    BEGIN
        SET @StringNum = CONVERT(NVARCHAR(254), @num)
        SET @StringNum = '0000' + @StringNum
    END
IF @num >= 10 AND @num <= 99 
    BEGIN
        SET @StringNum = CONVERT(NVARCHAR(254), @num)
        SET @StringNum = '000' + @StringNum
    END
IF @num >= 100 AND @num <= 999 
    BEGIN
        SET @StringNum = CONVERT(NVARCHAR(254), @num)
        SET @StringNum = '00' + @StringNum
    END
IF @num >= 1000 AND @num <= 9999 
    BEGIN
        SET @StringNum = CONVERT(NVARCHAR(254), @num)
        SET @StringNum = '0' + @StringNum
    END
IF @num >= 10000 AND @num <= 99999 
    BEGIN
        SET @StringNum = CONVERT(NVARCHAR(254), @num)
    END
IF @num = 100000 
    BEGIN
        SET @StringNum = '00000'
    END
SET @sNewInventoryPackNum = @sPartNumber + @StringNum + '10'
PRINT @sNewInventoryPackNum
RETURN @sNewInventoryPackNum
END

DECLARE @sNuwNum NVARCHAR(254)
EXEC @sNuwNum = _MG_NextInventoryPackNumberForPartNumber '77510002'
PRINT @sNuwNum

775100020000210
Msg 248, Level 16, State 1, Procedure _MG_NextInventoryPackNumberForPartNumber, Line 51
The conversion of the nvarchar value '775100020000210' overflowed an int column.
The '_MG_NextInventoryPackNumberForPartNumber' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
0