SQLTeam.com | Weblogs | Forums

SQL Stored Procedure


#1

I have an existing business database with tables and was wondering if someone could help me create a stored procedure.

The stored procedure which I would like creates needs to return the total sales value for a customer name based on the products ordered, quantity ordered and selling price. As well this will require a number of joins to get data from several tables, an expression to total the selling price, and a stored procedure that accepts an appropriate parameter. Return the stored procedure to return the total spend for a Mark Jacobi. The stored procedure should return the total spend and the name of the customer.

If someone could help me do this, it'll be greatly appreciated :slight_smile:


#2

first stab with sample data since you did not provide any

create table customers(customerid int , custsomernumber varchar(50), c
ustomerfirstname varchar(50), customerlastname varchar(50))
create table products(productid int , productnumber varchar(50), 
productdescr varchar(50) )
create table orders(productid int , customerid varchar(50), quantity int, price money)

insert into customers
select 1, 'D7878278', 'Mark', 'Jacobi' 

insert into products
select 1, 'FC8987', 'Widget 1'

insert into orders
select 1, 1, 3, 12.5


go

create proc dbo.totalspend_sp
(
	@customerid int
)
as
begin
	select c.customerfirstname, c.customerlastname, 
sum(quantity * price ) totalspend
	  from customers c
	  join orders o on c.customerid = o.customerid
	  join products p on p.productid = o.productid
	  where c.customerid = @customerid
	  group by c.customerfirstname, c.customerlastname

end

exec totalspend_sp 1