sql - SQL Server：必须声明标量变量

Question

我需要将全局变量声明为计数器

DECLARE @counter int
SET @counter = 0;

然后使用存储过程，但它总是告诉我@counter必须声明为标量变量

CREATE OR ALTER PROCEDURE proc_counter_type 
     @type NVARCHAR(15), 
     @dateFirst DATE,
     @dateSecond DATE
AS
    SELECT @counter = COUNT(title_id)
    FROM books
    WHERE type = @type AND (datum BETWEEN @dateFirst AND @dateSecond)

标签： sqlsql-serverstored-proceduresssms

Answer 1

您需要添加@Counter为OUTPUT参数：

CREATE OR ALTER PROCEDURE proc_counter_type @type nvarchar(15), @dateFirst date, @dateSecond date, @Counter int OUTPUT 
AS
BEGIN
    SELECT @counter = COUNT(title_id)
    FROM books
    WHERE type = @type
      AND (datum BETWEEN @dateFirst AND @dateSecond);
END;

然后你执行 SP 为：

DECLARE @counter int;
--Other Params
EXEC proc_counter_type, @Type, @dateFirst, @dateSecond, @Counter OUTPUT;

PRINT @Counter;