SQL Server Stored Procedure Update and Return Single Value

Question

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).

My stored procedure 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

My call to the stored procedure:

execute [spUpdateConfirmation] 2

I confirmed in the table the value was inserted but I get this error message:

2013050219072

(1 row(s) affected)

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

Question: What did I do wrong?

I understand what overflow is, the value is too large for an int, but I used the convert to varchar, inserted to table column type varchar(30)

I also tested this statement in SQL and it works fine:

print replace(replace(replace(convert(varchar(16),CURRENT_TIMESTAMP,120),'-',''),' ',''),':','')+convert(varchar(24),2)

It returns: 2013050219162

Answer 1

RETURN from a stored procedure only allows integer values. Specifically, the documentation states:

RETURN [ integer_expression ]

If you want to return a varchar value, you can use an output parameter

CREATE PROCEDURE [dbo].[spUpdateConfirmation]
@ReportID int, @Confirmation varchar(30) output
AS
--BEGIN
SET @Confirmation = 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
GO

To be called like this

declare @reportID int; -- set @reportID
declare @confirmation varchar(30);
exec [dbo].[spUpdateConfirmation] @reportID, @confirmation output;
-- @confirmation now contains the value set from the SP call above

A simpler option if you are calling this from C# is to SELECT the output as a single-row, single-column result and use SqlCommand.ExecuteScalar, e.g.

CREATE PROCEDURE [dbo].[spUpdateConfirmation]
@ReportID int
AS
--BEGIN
DECLARE @Confirmation varchar(30);
SET @Confirmation = replace(replace(replace(convert(varchar(16),CURRENT_TIMESTAMP,120),'-',''),' ',''),':','')+convert(varchar(24),@ReportID)
--PRINT @Confirmation
    SET NOCOUNT ON; -- prevent rowcount messages
    UPDATE Report
    SET  Confirmation = @Confirmation
    WHERE ReportID = @ReportID;
    --RETURN @Confirmation
    SET NOCOUNT OFF; -- re-enable for the following select
    SELECT @Confirmation;   -- this is the value you get
--END
GO