Question

sql server

please help me>>>

Create a stored procedure called uspRetrieveOrders that returns all the orders for a last name. It should return first name, last name, item price, discount amount, quantity, the line item extended amount ((ItemPrice-DiscountAmount)*Quantity), the tax amount, and the card type. It should accept LastName as a parameter. Include logic to test if the customer exists in the database. If not, generate an error message. Show the code you used to test the proc.

Answer 1

CREATE PROCEDURE uspRetrieveOrders
(
@lastname VARCHAR(20)
)
AS
BEGIN
IF EXISTS (SELECT * FROM Customer WHERE LastName=@lastname)
BEGIN
SELECT firstname, lastname, itemprice, discountamount, quantity,
((ItemPrice-DiscountAmount)*Quantity) AS ItemExtendedAmount,taxamount,cardtype
FROM Orders WHERE lastname=@lastname
END
ELSE
BEGIN
RETURN 'Not Found'
END
END