SQL Stored procedure to obtain top customers

Question

I'm trying to create a stored procedure that goes through a "SALES" table and returns the best two customers of a pharmacy (the two customers who have spent more money).

Here's some code:

Table creation:

create table Customer (
    Id_customer int identity(1,1) Primary Key,
    Name varchar(30),
    Address varchar(30),
    DOB datetime,
    ID_number int not null check (ID_number > 0),
    Contributor int not null check (Contributor > 0),
    Customer_number int not null check (Customer_number > 0)
    )

create table Sale (
    Id_sale int identity(1,1) Primary Key,
    Id_customer int not null references Customer(Id_customer),
    Sale_date datetime,
    total_without_tax money,
    total_with_tax money
    )

Well, I don't know if this is useful but I have a function that returns the total amount spent by a customer as long as I provide the customer's ID.

Here it is:

CREATE FUNCTION [dbo].[fGetTotalSpent]
(
    @Id_customer int
)
RETURNS money
AS
BEGIN
    declare @total money
    set @total = (select sum(total_with_tax) as 'Total Spent' from Sale where Id_customer=@Id_customer)
    return @total
END

Can someone help me get the two top customers?

Thanks Chiapa

PS: Here's some data to insert so you can test it better:

insert into customer values ('Jack', 'Big street', '1975.02.01', 123456789, 123456789, 2234567891)
insert into customer values ('Jim', 'Little street', '1985.02.01', 223456789, 223456789, 2234567891)
insert into customer values ('John', 'Large street', '1977.02.01', 323456789, 323456789, 3234567891)
insert into customer values ('Jenny', 'Huge street', '1979.02.01', 423456789, 423456789, 4234567891)

insert into sale values (1, '2013.04.30', null, 20)
insert into sale values (2, '2013.05.22', null, 10)
insert into sale values (3, '2013.03.29', null, 30)
insert into sale values (1, '2013.05.19', null, 34)
insert into sale values (1, '2013.06.04', null, 21)
insert into sale values (2, '2013.06.01', null, 10)
insert into sale values (2, '2013.05.08', null, 26)

Answer 1

You can do this with a single query without any special functions:

select top 2 c.id_customer, c.name, sum(s.total_with_tax)
from customer c
join sale s on c.id_customer = s.id_customer
group by c.id_customer, c.name
order by sum(s.total_with_tax) desc