|
Home > Archive > MS SQL Server > March 2006 > How to let a SP be shorter?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to let a SP be shorter?
|
|
| Jason Huang 2006-03-27, 3:23 am |
| Hi,
The following is one of our stored procedures,
is it possible to make it shorter?
Thanks for help.
Jason
ALTER PROCEDURE UpdateCustomerBtoRoo
t
AS
SET nocount on
UPDATE Customer SET
CustCName = (SELECT CustCName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
CustEName =(SELECT CustEName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
AbbrName=(SELECT AbbrName FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
ZipCode =(SELECT ZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Addr = (SELECT Addr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
EZipCode = (SELECT EZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillZipCode = (SELECT BillZipCode FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
BillAddr = (SELECT BillAddr FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Phone = (SELECT Phone FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Fax = (SELECT Fax FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Owner = (SELECT Owner FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
OwnerTitle = (SELECT OwnerTitle FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
HomePage = (SELECT HomePage FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
FirstDate = (SELECT FirstDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
DateUpDate = (SELECT DateUpDate FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
Area = (SELECT Area FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo),
WeightMeasure =(SELECT WeightMeasure FROM CustomerB WHERE
CustomerB.CustNo=Customer.CustNo)
WHERE EXISTS (SELECT * FROM
CustomerB WHERE CustomerB.CustNo=Customer.CustNo)
GO
| |
| Razvan Socol 2006-03-27, 3:23 am |
| Hi, Jason
Use something like this:
UPDATE Customer
SET CustCName=B.CustCName,
CustEName=B.CustEName,
AbbrName=B.AbbrName,
[...]
FROM Customer as C INNER JOIN CustomerB as B
ON C.CustNo=B.CustNo
In order to get the expected results, make sure that the CustNo is a
unique key or a primary key in both tables. Although easier to read,
this syntax is less portable than the original statement (the original
syntax was ANSI standard, but this is proprietary to Microsoft SQL
Server).
Razvan
|
|
|
|
|