title: SQL Server Stored Procedure Update and Return Single Value
body: <p>I need to call a stored procedure, give it a report id (int) and have it update a report table with a confirmation number (varchar) (confirmation # generated in the stored procedure) and return the confirmation number (i will need it to return it to a web service/website).</p>
  
  <p>My stored procedure code:</p>
  
  <pre><code>DECLARE PROCEDURE [dbo].[spUpdateConfirmation]
  @ReportID int
  AS
  BEGIN
     Declare @Confirmation varchar(30) = replace(replace(replace(convert(varchar(16),CURRENT_TIMESTAMP,120),'-',''),' ',''),':','')+convert(varchar(24),@ReportID)
     PRINT @Confirmation
  
     UPDATE Report
     SET  Confirmation = @Confirmation
     WHERE ReportID = @ReportID;
  
     RETURN @Confirmation
  END
  </code></pre>
  
  <p>My call to the stored procedure:</p>
  
  <pre><code>execute [spUpdateConfirmation] 2
  </code></pre>
  
  <p><strong>I confirmed in the table the value was inserted but I get this error message:</strong></p>
  
  <pre><code>2013050219072
  
  (1 row(s) affected)
  </code></pre>
  
  <blockquote>
    <p>Msg 248, Level 16, State 1, Procedure spUpdateConfirmation, Line 12<br>
    The conversion of the varchar value '2013050219072' overflowed an int column.<br>
    The 'spUpdateConfirmation' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.</p>
  </blockquote>
  
  <p>Question:  What did I do wrong?  </p>
  
  <p>I understand what overflow is, the value is too large for an int, but I used the convert to <code>varchar</code>, inserted to table column type <code>varchar(30)</code> </p>
  
  <p>I also tested this statement in SQL and it works fine:</p>
  
  <pre><code>print replace(replace(replace(convert(varchar(16),CURRENT_TIMESTAMP,120),'-',''),' ',''),':','')+convert(varchar(24),2)
  </code></pre>
  
  <p>It returns: 2013050219162</p>