sql - "Must declare the scalar variable"执行存储过程时出错

Question

我已尝试制作一个插入价格的程序:

create procedure prInsertPrice
@NuggetID varchar(5),
@Unit_Price money,
@Start_Date datetime,
@End_Date datetime
as
begin
DECLARE @date AS DATETIME
SET @date = GETDATE()
    if
        (
        (@NuggetID like 'N[0-9][0-9]')
        and
        (@Unit_Price is not null)
        and
        (@Start_Date is not null)
        )
    begin
        print 'Insert Success'
        insert NuggetPrice (NuggetId, Unit_Price, Start_Date, End_Date)
        values (@NuggetID, @Unit_Price, @Start_Date, @End_Date)
    end
    else
    begin
        print 'Failed to insert'
    end
end

当我执行程序时很好，但是当我像这样运行程序时:

EXEC prInsertPrice 'N01', 20000, @date, null

我收到错误消息:

Must declare the scalar variable @date.

为什么会这样，我该如何解决这个问题？

Answer 1

exec 语句中的@date 与存储过程中的不同。

你应该这样做:

DECLARE @date AS DATETIME
SET @date = GETDATE()
EXEC prInsertPrice 'N01', 20000, @date, null