"Must declare the scalar variable" error when executing a stored procedure

Question

I have tried to make a procedure to insert a price:

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

When I execute the procedure it's fine, but when I run the procedure like this:

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

I get the error message:

Must declare the scalar variable @date.

Why is this and how can I correct the problem?

The error message is pretty clear, you have to add `DECLARE @date datetime` and set this variable in the same batch as you run the exec. — HoneyBadger, Jan 11 '17 at 15:24
Sometimes it helps to know what you are trying to do, before we can give an acceptable answer. so... *WHAT WAS YOUR INTENTION HERE?* what were you expecting to happen? — Stephen, Jan 11 '17 at 15:48
I am trying to create a procedure of which will be able to do an insert based on the contents of the procedur. Well here I have a problem with variable declaration, which I was required to use `getdate()` function to get the current time when the exec the proc. sorry for bad english, isn't my native language. — Aditya Rizky, Jan 11 '17 at 15:59

score 5 · Answer 1 · answered Jan 11 '17 at 15:24

5

The @date in the exec statement is different then the one in the stored proc.

You should do something like:

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

answered Jan 11 '17 at 15:24

Peter4499

685
7
15

score 3 · Answer 2 · edited May 23 '17 at 12:01

When you run:

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

You are passing the variable @date as the third parameter to your stored procedure, as @Start_Date. This is entirely separate from the @date variable which you have declared inside the stored procedure itself, which gets declared and initialised after the procedure has been called, as it executes.

If you have not initialised the @date variable which is being passed as a parameter to the stored procedure before calling the stored procedure, then you will get the error you have described.

So, you need to declare and initialise this variable first:

DECLARE @date DATETIME = '2017-01-01' -- You can whatever date value you require here
EXEC prInsertPrice 'N01', 20000, @date, null

This should prevent the error.

Note: You can also separate the declaration and initialisation of the @date variable if you would prefer:

DECLARE @date DATETIME
SET @date = '2017-01-01'

In addressing your underlying problem though or preventing bad data being inserted into your NuggetPrice table though, I would agree with Prdp's suggestion of adding a CHECK Constraint to the table, for example:

ALTER TABLE NuggetPrice
  ADD CONSTRAINT CK_NuggetPrice CHECK (NuggetID LIKE 'N[0-9][0-9]' 
                                       AND Unit_Price IS NOT NULL 
                                       AND Start_Date IS NOT NULL)

This would also prevent anyone from inserting records which do not agree with the logic specified.

Pரதீப் · Answer 3 · 2017-01-11T16:58:59.503

1

Already other two answers gave enough information on the reason for error so am not going to talk about it. Here is different approach to solve the data validation

Instead of creating a Stored Procedure to restrict inserting bad data into table, I would suggest you to create a Check constraint to do this

ALTER TABLE NuggetPrice
  ADD CONSTRAINT CK_NuggetPrice CHECK (NuggetID LIKE 'N[0-9][0-9]' AND Unit_Price IS NOT NULL AND Start_Date IS NOT NULL)

This will make sure nobody inserts bad data in NuggetPrice

edited Jan 11 '17 at 16:58

answered Jan 11 '17 at 15:34

Pரதீப்

91,748
19
131
172

While I agree this might be a better solution to the underlying problem of data validation, this answer doesn't help the OP understand *why* he was getting the specific error in his question. – 3N1GM4 Jan 11 '17 at 16:56
@3N1GM4 Thats because already you guys answered on the reason for error – Pரதீப் Jan 11 '17 at 16:59
fair enough, I understand. Would you object to me including your suggestion in my answer to improve it? Equally, feel free to take my answer and work it into yours if you'd prefer. – 3N1GM4 Jan 11 '17 at 17:15
@3N1GM4 - Not at all.. please improve your answer – Pரதீப் Jan 11 '17 at 17:16

"Must declare the scalar variable" error when executing a stored procedure

3 Answers3