SQL Server stored procedure for Insert runs, but does not insert values into the table

Question

I've written this stored procedure to add line items into a table called InvoiceLineItems. The database does have a few test entries, and this procedure is based off of other insert stored procedures that are working.

For some reason, the query will execute successfully, but the values are not actually inserted into the table, and I can't figure out why, especially since the other insert procedures have worked.

Code that does successfully insert values into a table:

IF OBJECT_ID('sp_InsertInvoices') IS NOT NULL
    DROP PROC sp_InsertInvoices;
GO

CREATE PROC sp_InsertInvoices
     @CustomerID INT = NULL,
     @TotalDue MONEY = NULL,
     @Paid CHAR(3) = NULL
AS
BEGIN
    IF @CustomerID IS NULL
        THROW 50001, 'CustomerID cannot be null.', 1;

    IF @TotalDue IS NULL
        THROW 50001, 'TotalDue cannot be Null.', 1;

    IF @TotalDue < 0
        THROW 50001, 'TotalDue cannot be negative.', 1;

    IF @Paid IS NULL
        THROW 50001, 'Must specify if the balance is paid or not.', 1;
    ELSE --All data is validated
        INSERT INTO Invoices (CustomerID, TotalDue, Paid)
        VALUES (@CustomerID, @TotalDue, @Paid)
END
GO

The code that doesn't insert values:

IF OBJECT_ID('sp_InsertInvoiceLineitems') IS NOT NULL
    DROP PROC sp_InsertInvoiceLineitems;
GO

CREATE PROC sp_InsertInvoiceLineitems
     @InvoiceID INT = NULL,
     @ProductID INT = NULL,
     @PQty TINYINT = NULL,
     @ListPrice MONEY = NULL, 
     @ServiceID INT = NULL,
     @ServiceCost MONEY = NULL, 
     @SQty TINYINT = NULL,
     @TotalAmount MONEY = NULL
AS
BEGIN
    IF @InvoiceID IS NULL
        THROW 50001, 'Must enter an InvoiceID.', 1;

    IF @ProductID IS NOT NULL AND @ProductID NOT IN (SELECT ProductID FROM Products)
        THROW 50001, 'Please enter a valid ProductID.', 1;

    IF @PQty IS NOT NULL AND @ProductID IS NOT NULL AND @PQty <= 0
        THROW 50001, 'PQty must be greater than zero.', 1;

    IF @ProductID IS NOT NULL
        SET @ListPrice = (SELECT ListPrice FROM Products WHERE ProductID = @ProductID);

    IF @ServiceID IS NOT NULL AND @ServiceID NOT IN (SELECT ServiceID FROM Services)
        THROW 50001, 'Please enter a valid ServiceID.', 1;

    IF @ServiceID IS NOT NULL
        SET @ServiceCost = (SELECT ServiceCost FROM Services WHERE ServiceID = @ServiceID);

    IF @SQty IS NOT NULL AND @ServiceID IS NOT NULL AND @Sqty <= 0
        THROW 50001, 'SQty must be greater than zero.', 1;

    IF @ProductID IS NOT NULL OR @ServiceID IS NOT NULL
        SET @TotalAmount = ((@ListPrice*@PQty) + (@ServiceCost*@SQty));
    ELSE --All data is verified
        INSERT INTO InvoiceLineItems (InvoiceID, ProductID, PQty, ListPrice, ServiceID, ServiceCost, SQty, TotalAmount)
        VALUES (@InvoiceID, @ProductID, @PQty, @ListPrice, @ServiceID, @ServiceCost, @SQty, @TotalAmount);
END
GO

Any thoughts on why it isn't inserting the values?

Can you please format your code? This is borderline unreadable. — John Wu
– John Wu, Commented Jun 10, 2016 at 20:18
Sorry, this is my first time using this site. How would I format it? When I open it it looks right, so unsure what I should do. — BriMead
– BriMead, Commented Jun 10, 2016 at 20:24

S3S · Accepted Answer · 2016-06-10 20:37:02Z

1

In IF...ELSE, the ELSE or supplemental IF are only executed when the preceding IF statement(s) are not satisfied. Therefore:

IF @ProductID IS NOT NULL SET @ListPrice = (SELECT ListPrice FROM Products WHERE ProductID = @ProductID)

If this evaluates to TRUE your statement will terminate, and you will have set a @ListPrice.

You need to check for ALL errors like you did in your first statement, and if ALL conditions are not met (there are no errors) then set your variables and update (insert) into your table.

answered Jun 10, 2016 at 20:37

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BriMead Over a year ago

Thanks! I honestly forgot about that with the IF statements, and I'd been looking at it for so long that it wasn't even coming to mind. But it works now! Thanks!

S3S Over a year ago

No worries at all!

Collectives™ on Stack Overflow

SQL Server stored procedure for Insert runs, but does not insert values into the table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related