title: null in return value in sql procedure
body: <p>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:</p>
  
  <pre><code>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 &gt;= 1 AND @num &lt;= 9 
      BEGIN
          SET @StringNum = CONVERT(NVARCHAR(254), @num)
          SET @StringNum = '0000' + @StringNum
      END
  IF @num &gt;= 10 AND @num &lt;= 99 
      BEGIN
          SET @StringNum = CONVERT(NVARCHAR(254), @num)
          SET @StringNum = '000' + @StringNum
      END
  IF @num &gt;= 100 AND @num &lt;= 999 
      BEGIN
          SET @StringNum = CONVERT(NVARCHAR(254), @num)
          SET @StringNum = '00' + @StringNum
      END
  IF @num &gt;= 1000 AND @num &lt;= 9999 
      BEGIN
          SET @StringNum = CONVERT(NVARCHAR(254), @num)
          SET @StringNum = '0' + @StringNum
      END
  IF @num &gt;= 10000 AND @num &lt;= 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
  </code></pre>
  
  <p>This is how I try to call the procedure:</p>
  
  <pre><code>DECLARE @sNuwNum NVARCHAR(254)
  EXEC @sNuwNum = _MG_NextInventoryPackNumberForPartNumber '77510002'
  PRINT @sNuwNum
  </code></pre>
  
  <p>The procedure should return this value: 775100020000210. But all I get in the console part of the screen is this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I'm using SQL server 2008.
  I'm quite sure that I have to change something small, but could you please tell me what?</p>