Create a Store Procedure by getting info from others table!

[luckyguy]

Im going to create a store procedure to update the customer status under customer table.My assumptinos is if the "amount" field of salesorders(this is the different table) over $1000,we will name him as "good" customer in the "type" field,and the default one is "normal".The problem is the "type" field is in the customer table and the "amount" field is in the salesorder table!how can i create a store procedure in the customer table by using the "amount" in the salesorders table?Its urgent!Can someone help me?thankx alot!

 

[tlbroadbent]

Stored procedures are stored in databases not tables. A stored procedure can read and update data in tables of the same database, other databases and even on other servers. You simply need to use the complete name of the table to access it along with appropriate permissions.

Here are a copule of methods to do what you want. The answers are similar to answers provided to you in another thread. You can use either method in a stored procedure that you would create in the database containing the two tables.

Update Customer
Set Type='Good'
If Exists
(Select * From SalesOrder
Where SalesOrder.CustomerID=Customer.CustomerID
And Amount>=1000)

Update Customer
Set Type='Good'
From Customer c
Join SalesOrder s
On c.CustomerID=s.CustomerID
Where s.Amount>=1000 Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[AWithers]

Possibly the best way to implement the code suggested via terry would beusing a trigger on the salesorder table

Andy