T-SQL存储过程-无法将输出值分配给变量

Question

我有一个存储过程，它查找当前BatchID并将其递增1，然后将该值作为输出参数返回；但是，每当我在后面使用select语句时，我都会在执行存储过程之前为其设置值。

为什么会发生这种情况？

存储过程的执行：

declare @BatchStartTS datetime = getdate()
declare @NewBatchID int = 0
exec dbo.[usp_CreateBatchRecord] @BatchStartTS, @NewBatchID output
select [NewBatchID] = @NewBatchID

存储过程：

IF OBJECT_ID('dbo.usp_CreateBatchRecord') IS NOT NULL 
DROP PROCEDURE dbo.usp_CreateBatchRecord
GO
CREATE PROCEDURE dbo.usp_CreateBatchRecord
@BatchDate DATETIME = NULL,
@BatchID INT OUTPUT
AS
BEGIN
/*  PROCEDURE       dbo.[usp_CreateBatchRecord]
DESCRIPTION:    Creates a new Import Batch record and returns the next BatchID
PARAMETERS
+ Required:    N/A
+ Optional:    @BatchDate
OUTPUT:         @BatchID - returns the BatchID of the new Batch record created
MODIFIED:       N/A
EXAMPLE(S):
-- must set optional parameter to DEFAULT value AND declare output parameter
declare @NewBatchID int = null
exec dbo.[usp_CreateBatchRecord] DEFAULT,@NewBatchID OUTPUT
-- must declare output parameter
declare @NewBatchID int = null
exec dbo.[usp_CreateBatchRecord] '2016-06-26 15:11:00',@NewBatchID OUTPUT
*/
SET NOCOUNT ON;
DECLARE @ErrMsg VARCHAR(MAX) = NULL
DECLARE @ErrNum INT = NULL
DECLARE @ErrLineNo INT = NULL
/* PARAMETER CHECKING */
if ISNULL(LEN(@BatchDate), 0) = 0
SET @BatchDate = GETDATE()
IF OBJECT_ID('dbo.Batch') IS NULL 
BEGIN
SET @ErrMsg = ERROR_MESSAGE()
SET @ErrLineNo = ERROR_LINE()
SET @ErrNum = ERROR_NUMBER()
GOTO ERR_HANDLING
END
/* INTERNAL VARIABLES */
DECLARE @CurrentMaxBatchID INT = (SELECT MAX(BatchID) FROM dbo.Batch)
DECLARE @NextBatchID INT = @CurrentMaxBatchID + 1
BEGIN TRY
/* ACTION */
INSERT INTO dbo.Batch (BatchID, BatchStartTS)
SELECT @NextBatchID, @BatchDate
/* RETURN VALUE(S) */
SELECT @NextBatchID
GOTO THE_END
END TRY
BEGIN CATCH
SET @ErrMsg = ERROR_MESSAGE()
SET @ErrLineNo = ERROR_LINE()
SET @ErrNum = ERROR_NUMBER()
END CATCH
ERR_HANDLING:
RAISERROR('<!> ERROR:  (%i) [%i] %s',16,1,@ErrNum,@ErrLineNo,@ErrMsg) WITH NOWAIT
THE_END:
RETURN
END

Answer 1

非常感谢Jeroen Mostert！我甚至没有意识到我从未设置过输出变量！

[Jeroen Mostert]您不会在过程中的任何位置设置@BatchID的值。也许您打算编写SELECT@BatchID=@NextBatchID，而不是将其作为结果集。