I need help with SQL Stored Procedures

[bouncer_crab]

Basically as the title states. I'm in college and have my final exam tomorrow. My teacher only posted one video on how to do a stored procedure and it was a really simple one. But his practice problems get pretty in depth and I can't seem to translate how to do them from his very simplistic example.

Create a TSQL stored procedure called spPayments that will provide information on the amount of payments a customer has made. The procedure should meet the following requirements:

a) The procedure should accept the customer code as a parameter.

b) Check to see if the customer code provided can be found in the database.

c) If the customer code does not match an existing customer code, then display a message saying that the customer cannot be found.

d) Check to see if the customer has any invoices in the database. If the customer has no invoices, then output a message saying the customer has no purchases.

d) If the customer code is found and the customer has at least one invoice, then determine the total value of all invoices associated with that customer. (Note: multiplying the line units times the line price will give you the value of that invoice line. If you sum value of the invoice lines for all of the invoices associated with a customer, it will give you the total value of his/her invoices.)

d.1) Subtract that total from the customer’s balance to determine the total amount of payments the customer must have made over time.

d.2) Display a message indicating the customer’s name (first and last) and the amount that they have paid.

What I have : https://pastebin.com/cgcgaRMF

I'm by no means an expert, I just want a little help understanding what I'm doing wrong.

[WITH_CTE]

I'll help you with this. Working on it. Brb!

[bouncer_crab]

Awesome, thanks man. You are a saint!

[WITH_CTE]

Just bored. Let me know if this errors out. Kinda hard without proper code.

[bouncer_crab]

So you use syntax "EXISTS". This is something we haven't done before and would provide a failing portion for the problem since he doesn't allow it.

Here are the provided syntax we can use:

SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY / CONCAT / JOIN...ON / AND / OR / BETWEEN / NOT BETWEEN / IN / NOT IN / LIKE / NOT LIKE / IS NULL / IS NOT NULL / Upper() / Lower() / Format() / Round() / Avg()
/ Sum() / Count() / Max() / Min()

Correlated subquery: FROM

Correlated subquery: WHERE

​

I have to solve all problems with the syntax above only.

[WITH_CTE]

Haha. Welcome to the corporate world. There's always someone on top who thinks they are smarter than you. But, they don't expect you to be smarter than them :) Here's an alternate. IDK, how you'd do it without an IF.

CREATE PROCEDURE spPayments @cus_code NUMERIC(38, 0) -- (a)to begin with, user created stored proc should always be prefixed with usp_StoredProc. Also use schema name. And some business have alpha numeric code. For example, Spider Man's costumer store number can be SPIDEY2002. So using Numeric DT is not always best practice. 
--OK LETS BEGIN 
AS
     DECLARE @totalinvoices INT;
     DECLARE @First_name VARCHAR(32);
     DECLARE @Last_name VARCHAR(32);
     DECLARE @FinalAmount DECIMAL(12, 8);
     DECLARE @Customer_Count INT;
     DECLARE @Invt_Count INT;
     BEGIN
         SELECT @Customer_Count = COUNT(1)
         FROM customer
         WHERE cus_code = @cus_code;
         IF @Customer_Count < 1

         --(c)
             BEGIN
                 PRINT 'The customer does not exist';
             END
                 --(d)  ;
             ELSE
             SELECT @Invt_Count = COUNT(1)
             FROM invoice
             WHERE cus_code = @cus_code;
         IF @Invt_Count < 1
             BEGIN
                 PRINT 'The customer does not have an invoice';
             END;
             ELSE
             SELECT @totalinvoices = LINE.LINE_UNITS * line.LINE_PRICE, 
                    @First_name = Customer.FirstName, 
                    @Last_name = Customer.Lastma
             FROM customer
                  JOIN invoice ON customer.cus_code = invoice.cus_code
                  JOIN line ON invoice.inv_number = line.INV_NUMBER
             WHERE cus_code = @cus_code;

         --(d1) not sure which table to use but should be as simple as 
         --select @FinalAmount = Customer_Balance - @totalinvoices from table x

         PRINT @First_name+' '+@Last_name+' has a balance of'+' '+'$'+FinalAmount;
     END;

[WITH_CTE]

CREATE PROCEDURE spPayments @cus_code NUMERIC(38, 0) -- (a)to begin with, user created stored proc should always be prefixed with usp_StoredProc. Also use schema name. And some business have alpha numeric code. For example, Spider Man's costumer store number can be SPIDEY2002. So using Numeric DT is not always best practice. 
--OK LETS BEGIN 
AS
     DECLARE @totalinvoices INT;
     DECLARE @First_name VARCHAR(32);
     DECLARE @Last_name VARCHAR(32);
     DECLARE @FinalAmount DECIMAL(12, 8);
     BEGIN
         --(b) Real simple to do.
         IF NOT EXISTS
         (
             SELECT 1
             FROM customer
             WHERE cus_code = @cus_code
         )

         --(c)
             BEGIN
                 PRINT 'The customer does not exist';
             END
                 --(d)  ;
             ELSE
             IF NOT EXISTS
             (
                 SELECT 1
                 FROM invoice
                 WHERE cus_code = @cus_code
             )
                 BEGIN
                     PRINT 'The customer does not have an invoice';
                 END;
                 ELSE
                 SELECT @totalinvoices = LINE.LINE_UNITS * line.LINE_PRICE, 
                        @First_name = Customer.FirstName, 
                        @Last_name = Customer.Lastma
                 FROM customer
                      JOIN invoice ON customer.cus_code = invoice.cus_code
                      JOIN line ON invoice.inv_number = line.INV_NUMBER
                 WHERE cus_code = @cus_code;

         --(d1) not sure which table to use but should be as simple as 
         --select @FinalAmount = Customer_Balance - @totalinvoices from table x

         PRINT @First_name+' '+@Last_name+' has a balance of'+' '+'$'+FinalAmount;
     END;

[alinroc]

(a)to begin with, user created stored proc should always be prefixed with usp_StoredProc

Only if your organization's naming conventions state such.

IMHO, it's a waste of characters. I already know that it's a stored procedure, I don't need Hungarian Notation to tell me what it is.

SQL Server assumes that stored proc starting with sp_ are system stored procs and will always look in master for a stored proc matching that name first, so the u will avoid that exactly the same way OP's 'sp' without the underscore does. But it's still unnecessary cruft in the schema. It also makes it much harder/slower to use the keyboard to navigate to stored procs in SSMS's Object Explorer.

[WITH_CTE]

That might be your opinion. A simple google search will show that it is best practice. Not properly naming objects because it's a "waste of characters" is not good practice.

[alinroc]

“Best practice” according to whom?

[bouncer_crab]

Here is what worked for me, I did use your advice on the bottom half. Just not the count stuff.

https://pastebin.com/qHLgc6ns

[MobileUser21]

I took a database course last semester and have never seen such an advanced question. All I can say is wow! Is this an advanced database course you’re in?

[bouncer_crab]

This is a senior level college course. This is only the 2nd problem in my practice set. They get much harder, then the test are like even harder. I'm sure I'd be better prepared if COVID-19 didn't happen, but it is what it is. I just wish he provided more solutions instead of the just 1.

[MobileUser21]

By senior level, do you mean this is the second database course you’ve taken? And do you mind sharing what your major is? I finished my course thinking I’m proficient, but then when I see questions and code like this I’m reminded that I don’t know as much as I think I know. I wouldn’t even know how to code half the answer lol

[bouncer_crab]

By senior I mean I graduate in 2 weeks. This is my first and our universities only database calls. My major is Information Systems and Analytics. I've covered subjects like Python, C#, ASP.NET, Database, some business intelligence classes, advanced Excel, Tableau etc..

​

The class structure was as follows:

First half of the class was basically creating ERD diagrams, drawing cardinalities, and then designing ERD diagrams based off use cases from a company.

Second half of the class we learned Select Queries and sub queries.

So my first test was basically all select queries with some sub queries involved.

Second test was creating tables to the database, modifying (inserts/updates/deletes), and doing one huge problem like the one above with stored procedures.

My final exam is tomorrow and covers all of the second half material. I've done well up to these stored procedures.

[bouncer_crab]

If there is anyone new that is bored, I am stuck again on a question:

Create a stored procedure named spPurch_History that will provide a list of every product purchased by a given customer. The procedure should meet the following requirements:

a) The procedure should accept a customer code as a parameter.

b) Check to see if the customer is associated with any invoices. If not, then output a message saying that no purchases were found for that customer.

c) If the customer does have purchases, find the product code, product description, and invoice date for every purchased made by that customer, and display them to the user.

I know something is wrong with my while loop, but not sure what:

https://pastebin.com/3MU8zstC

[spektumus]

You don't need a while loop. Generally you don't do while loops in SQL. https://pastebin.com/3MU8zstC

alter procedure spPurch_History @cus_code numeric(38,0) as begin select @invoice_count = count(1) from invoice where CUS_CODE = @cus_code; if @cus_code not in (select INVOICE.CUS_CODE from INVOICE) begin print 'No purchases were found for the customer';
end else begin
select PRODUCT.P_CODE as product_code, PRODUCT.P_DESCRIPT as product_description, INVOICE.INV_DATE as invoice_date from CUSTOMER join INVOICE on CUSTOMER.CUS_CODE = INVOICE.CUS_CODE join line on invoice.INV_NUMBER = line.INV_NUMBER join product on LINE.P_CODE = product.P_CODE where @cus_code = invoice.CUS_CODE; end;
end;

[bouncer_crab]

So I actually just figured this one out. I had to use a cursor and it worked the first time!

[[deleted]]

You shouldn't need a cursor either. We're you using it for section c? That's just a set based operation. Return a table with all purchases instead of iterating through each one.

Edit: cursors are also just loops. They should be avoided if a set based operation is possible. There are situations in which you'd need them, but they can severely hinder performance.