Sum and calculations for each customer in SQL stored procedure

Question

In a SQL database I got storage information for each user, for each customer.

I need to write a stored procedure that sums the disc usage (MB) for the customers users. When I got the total sum of all users for a single customer (totalDiscUsage), I need to perform a calculation (simple example):

x = numberOfUsers * 200
y = (totalDiscUsage - x) / (10 * 5)

After that, I need to write y to the database, and do that for all customers.

My question is how I can do this the best way?

Maybe using a cursor to go through each customer, perform the sum and calculation, and write the result to the database? Would that be a good solution?

Thanks in advance. Help will be much appreciated!

Answer 1

Please - do not go around using cursors again! :-) SQL is set-based - avoid cursors whenever you can!

And here you can - easily. So for each customer, you need to determine the number of users first, and then do a simple calculation, and update the customer.

My suggestion would be:

• create a little function that calculates the number of users for a given customer
• create a second little function to do the same to calculate the total disk usage

• write your stored proc as a simple update statement that does the calculation

  CREATE PROCEDURE dbo.UpdateCustomers()
  AS BEGIN
      UPDATE Customers
      SET y = (dbo.GetTotalDiskUsage(CustomerID) - dbo.GetTotalUsers(CustomerID) * 200) / 50
  END 
  

Seems too easy, almost :-)

Marc