SQL Server Stored Procedure Update and Return Single Value

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

Answers


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

Need Your Help

What's the quickest way to dump & load a MySQL InnoDB database using mysqldump?

mysql database backup innodb

I would like to create a copy of a database with approximately 40 InnoDB tables and around 1.5GB of data with mysqldump and MySQL 5.1.

Entity System in C++

c++ architecture entity-system component-based

I've recently discover the Entity System architecture and i've got some difficulties to do it in C++ / understand implementation.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.