# SQL Parameter validation

**URL:** <https://forum.codewithmosh.com/t/sql-parameter-validation/21354>\
**Category:** SQL\
**Created:** [July 9, 2023, 6:25am UTC](https://forum.codewithmosh.com/t/sql-parameter-validation/21354 "2023-07-09T06:25:29Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nav](https://avatars.discourse-cdn.com/v4/letter/n/6a8cbe/32.png) [@Nav](https://forum.codewithmosh.com/u/Nav)\
**Post date:** [July 9, 2023, 6:25am UTC](https://forum.codewithmosh.com/t/sql-parameter-validation/21354/1 "2023-07-09T06:25:29Z")

</div>

**SQL course \>\> Stored procedure \>\> 7. Parameter validation**

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**

---

<div class="post-metadata">

**Author:** ![lucidlear](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.codewithmosh.com/lucidlear/32/5477_2.png) [@lucidlear](https://forum.codewithmosh.com/u/lucidlear)\
**Post date:** [July 9, 2023, 7:56am UTC](https://forum.codewithmosh.com/t/sql-parameter-validation/21354/2 "2023-07-09T07:56:51Z")

</div>

im not sure why you have the stars there before and after the IF statement but that will give you errors

---

<div class="post-metadata">

**Author:** ![Nav](https://avatars.discourse-cdn.com/v4/letter/n/6a8cbe/32.png) [@Nav](https://forum.codewithmosh.com/u/Nav)\
**Post date:** [July 9, 2023, 8:29am UTC](https://forum.codewithmosh.com/t/sql-parameter-validation/21354/3 "2023-07-09T08:29:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![SAM](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.codewithmosh.com/sam/32/167_2.png) [@SAM](https://forum.codewithmosh.com/u/SAM)\
**Post date:** [July 9, 2023, 10:05am UTC](https://forum.codewithmosh.com/t/sql-parameter-validation/21354/4 "2023-07-09T10:05:39Z")

</div>

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 `SELECT`s. The following lectures in that section will show you how to do that.

---

<div class="post-metadata">

**Author:** ![Nav](https://avatars.discourse-cdn.com/v4/letter/n/6a8cbe/32.png) [@Nav](https://forum.codewithmosh.com/u/Nav)\
**Post date:** [July 9, 2023, 12:05pm UTC](https://forum.codewithmosh.com/t/sql-parameter-validation/21354/5 "2023-07-09T12:05:25Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![Ritu](https://avatars.discourse-cdn.com/v4/letter/r/7993a0/32.png) [@Ritu](https://forum.codewithmosh.com/u/Ritu)\
**Post date:** [April 28, 2024, 4:20am UTC](https://forum.codewithmosh.com/t/sql-parameter-validation/21354/6 "2024-04-28T04:20:43Z")

</div>

> [@Nav](#):
>
> UPDATE invoices i  
> SET i.payment\_total = payment\_total,  
> i.payment\_date = payment\_date  
> WHERE i.invoice\_id = invoice\_id;

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

```

END

EXECUTE SP\_Payment\_Validated 2,-100,‘2019-02-12’  
Invalid: Payment Amount \< 0

EXECUTE SP\_Payment\_Validated 2, 200,‘2019-02-12’  
Invalid: Payment Amount \> Invoice Total

EXECUTE SP\_Payment\_Validated 2, 175.32,‘2019-02-12’  
Payment Amount is OK