null in return value in sql procedure

Question

I'm trying to build a procedure that returns a nvarchar value. It seems to work fine up until I try to get the value from the procedure. This is all the code from my procedure:

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

This is how I try to call the procedure:

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

The procedure should return this value: 775100020000210. But all I get in the console part of the screen is this:

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

I'm using SQL server 2008. I'm quite sure that I have to change something small, but could you please tell me what?

Answer 1

Just to expand on what @PaulDraper and OzrenTkalcec have said, Sql PROCs may only return INT

So when you return an NVARCHAR SQL is trying to convert it to INT, which is overflowing (It is possible that this procedure has worked correctly previously if the return value has been small enough to fit into an INT, e.g. with a small value for @PartNumber)

You have several options:

Bind an Output variable

 ALTER PROCEDURE [dbo].[_MG_NextInventoryPackNumberForPartNumber]
    @sPartNumber NVARCHAR(254),
    @sNewInventoryPackNum NVARCHAR(254) OUTPUT
 AS 
 ...

and then SET @sNewInventoryPackNum = @sPartNumber + @StringNum + '10' will be the last line of the PROC. The caller will then need to bind to the Output parameter (e.g. ParameterDirection.Output from ADO)

Select the @sNewInventoryPackNum as a result, instead of returning it. This will then be a scalar resultset available to the caller (e.g. retrieve in ADO as a Scalar .ExecuteScalar)

Use a UDF with a NVARCHAR return value

As an aside, it appears that a major part of your PROC is concerned with padding with leading zeroes to keep a fixed string width. There are several alternatives, such as using STUFF, which offer more elegant ways of achieving this IMO.

Answer 2

SQL Server only allows an int (not bigint or varchar) to be returned. Instead use an output parameter.

http://www.toadworld.com/platforms/sql-server/w/wiki/10261.stored-procedures-output-parameters-return-values.aspx