Store Procedure!

[luckyguy]

I need to create a store procedure to update the customers status. And my assumptions is when the customer buy over $1000 we will name as GOOD customer in "TYPE" field in the customer table.can someone help me to solve this problems...thankx...!

 

[LFCfan]

How about this

CREATE PROCEDURE ChangeCustomerStatus
@spend INT
AS
UPDATE tblCustomers
SET Type = 'GOOD'
WHERE customer_spend > @spend

GO

And then to execute:

EXEC ChangeCustomerStatus 1000


HTH
Maria

 

[lottastuff]

I'm guessing that luckyguy is looking for how to set the type column when a purchase is made, not a one-shot that makes the change for all customers like the above.

If so, then you can either simply add the update statement as lfcfan has indicated (adding to the where clause something like " and customerid=@givenid".

Although triggers are supposed to be difficult to debug, you could also define a trigger tied to the update and the insert action on this table. The logic that is triggered simply checks the cumulative value of all orders for the given customer, and if they exceed a given threshold, then set the type column to "GOOD".

Is this more along the lines you had in mind?

Something like ...

create trigger trOrders_insupd on orders FOR INSERT, UPDATE
as
declare @goodthreshhold INT
declare @totalValue INT
declare @cusid INT
set @goodthreshhold = 1000
set @cusid = inserted.cusid
if update (ordertotal)
   select @totalValue = sum(ordertotal)
       from orders where o.cusid=@cusid 
       group by ordertotal
   if (@totalValue > @goodthreshhold) 
       update customers set type='Good' where cusid=@cusid

Cannot guarantee the syntax, but I think this would do the job if it's what you are looking to do.