SQL Server 存储过程更新并返回单个值

Question

我需要调用一个存储过程，给它一个报告ID(int)并让它用确认号(varchar)(在存储过程中生成的确认号)更新报告表并返回确认号(我将需要它才能将其返回到网络服务/网站)。

我的存储过程代码:

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

我对存储过程的调用:

execute [spUpdateConfirmation] 2

我确认表中已插入值，但收到此错误消息:

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.

问题:我做错了什么？

我明白什么是溢出，该值对于 int 来说太大了，但我使用了转换为 varchar，插入到表列类型 varchar(30)

我还在 SQL 中测试了这个语句，它工作得很好:

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

返回:2013050219162

Answer 1

RETURN存储过程中仅允许整数值。具体来说，文档指出:

RETURN [ integer_expression ]

如果想返回varchar值，可以使用输出参数

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

这样称呼

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

如果您从 C# 调用此函数，则更简单的选择是选择输出作为单行、单列结果并使用 SqlCommand.ExecuteScalar，例如

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