• 5 Subscribers
  • 114 Views
1

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?


3 answers in total

4
Peter4499 Posted at 2017-01-11 15:24:58Z

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
2
3N1GM4 Posted at 2017-01-11 15:32:47Z

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.

1
Prdp Posted at 2017-01-11 15:34:15Z

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

Answer this questsion