sql – Must declare the scalar variable error when executing a stored procedure

sql – Must declare the scalar variable error when executing a stored procedure

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

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 Prdps 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.

sql – Must declare the scalar variable error when executing a stored procedure

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

Leave a Reply

Your email address will not be published.