I am trying to enter an additional parameter validation where an alert message will be shown if we input a payment amount more than the invoice total.
After creating the stored procedure if I input any data the SQL is showing an error Unknown table ‘invoices’ in field list. Can anyone help me with the correct code for this and where I am doing wrong?
– write a stored procedure update_invoice
– Use parameter validation for payment amount
DROP PROCEDURE IF EXISTS update_invoice;
DELIMITER $$
CREATE PROCEDURE update_invoice
(
invoice_id INT,
Payment_total DECIMAL(9,2),
Payment_date DATE
)
BEGIN
IF payment_total <=0 OR payment_total > invoices.invoice_total
THEN
SIGNAL SQLSTATE ‘22003’
SET MESSAGE_TEXT = ‘Invalid payment amount’;
END IF;
UPDATE invoices i
SET i.payment_total = payment_total,
i.payment_date = payment_date
WHERE i.invoice_id = invoice_id;
END $$
DELIMITER ;
Error Code: 1109. Unknown table ‘invoices’ in field list
Ignore the stars. When I tried to bold the lines in the forum it was inserted.
– write a stored procedure update_invoice
– Use parameter validation for payment amount
DROP PROCEDURE IF EXISTS update_invoice;
DELIMITER $$
CREATE PROCEDURE update_invoice
(
invoice_id INT,
Payment_total DECIMAL(9,2),
Payment_date DATE
)
BEGIN
IF payment_total <=0
OR payment_total > invoices.invoice_total
THEN
SIGNAL SQLSTATE ‘22003’
SET MESSAGE_TEXT = ‘Invalid payment amount’;
END IF;
UPDATE invoices i
SET i.payment_total = payment_total,
i.payment_date = payment_date
WHERE i.invoice_id = invoice_id;
END $$
DELIMITER ;
Error Code: 1109. Unknown table ‘invoices’ in field list
How should MySQL know which invoice you are referring to when you write invoices.invoice_total?
If you want to access values from the database you need to retrieve them with SELECTs. The following lectures in that section will show you how to do that.
This worked. Thanks.
Used the SELECT to retrieve invoice total.
CREATE DEFINER=root@localhost PROCEDURE update_invoice(
invoice_id INT,
Payment_total DECIMAL(9,2),
Payment_date DATE
)
BEGIN
IF payment_total <=0
OR payment_total >
(SELECT inv.invoice_total
FROM invoices inv
WHERE inv.invoice_id = invoice_id)
THEN
SIGNAL SQLSTATE ‘22003’
SET MESSAGE_TEXT = ‘Invalid payment amount’;
END IF;
SQL Server:
USE SQL_Invoicing_2;
SELECT * FROM Invoices
– DROP PROCEDURE SP_Payment_Validated;
– CREATE / ALTER
CREATE PROCEDURE SP_Payment_Validated @Invoice_ID INT, @Payment_Amount DECIMAL (9,2), @Payment_Date DATE
AS
BEGIN
Declare @Msg varchar(255)
IF @Payment_Amount <= 0
Begin
SET @Msg = ‘Invalid: Payment Amount < 0’
RAISERROR(@Msg,10,1)
End
ELSE
IF @Payment_Amount >(Select Invoice_Total from Invoices
WHERE Invoice_ID =@Invoice_ID)
Begin
SET @Msg = ‘Invalid: Payment Amount > Invoice Total’
RAISERROR(@Msg,10,1)
End
ELSE
Begin
UPDATE Invoices
SET Payment_Total = @Payment_Amount,
Payment_Date = @Payment_Date
WHERE Invoice_ID = @Invoice_ID;
SET @Msg = 'Valid: Payment Amount'
RAISERROR(@Msg,10,1)
End