pothireddysunil
asked on
Optimizing storedprocedure
I am using Following stored procedure and function to read vendor values.Is there any way to optmize these.
CREATE FUNCTION dbo.fnVendorRead ()
RETURNS TABLE AS
RETURN (SELECT VENDOR_KEY,
VENDOR_NAME,
VENDOR_NAME + ' (' + VENDOR_KEY + ')' AS 'Vendor Formatted Name',
FOB,
TERMS,
CRLIMIT,
ACCTNUM
FROM dbo.VENDOR
UNION
SELECT VENDOR_KEY,
VENDOR_NAME,
VENDOR_NAME + ' (' + VENDOR_KEY + ')' AS 'Vendor Formatted Name',
FOB,
TERMS,
'',
''
FROM dbo.TempVendor)
CREATE PROCEDURE spVendorRead
@pVendorID VARCHAR(256)
AS
DECLARE @Phone INT
DECLARE @Fax INT
DECLARE @Cell INT
SET @Phone=1
SET @Fax=2
SET @Cell=3
SELECT VENDOR_KEY,
VENDOR_NAME,
VENDOR_NAME + ' (' + VENDOR_KEY + ')' AS 'Vendor Formatted Name',
FOB,
TERMS,
CRLIMIT,
ACCTNUM,
BuyerID,
STATUS
FROM dbo.VENDOR
LEFT JOIN dbo.BuyerVendor ON VENDOR_KEY = VendorID
WHERE VENDOR_KEY = @pVendorID
UNION
SELECT VENDOR_KEY,
VENDOR_NAME,
VENDOR_NAME + ' (' + VENDOR_KEY + ')' AS 'Vendor Formatted Name',
FOB,
TERMS,
'',
'',
BuyerID,
''
FROM dbo.TempVendor
LEFT JOIN dbo.BuyerVendor ON VENDOR_KEY = dbo.BuyerVendor.VendorID
WHERE VENDOR_KEY = @pVendorID
ORDER BY 'Vendor Formatted Name'
SELECT dbo.Address.AddressID,
OwnerID,
REPLACE(Address,CHAR(253),
City,
State,
Zip,
Country,
BuyerID
FROM dbo.Address
LEFT JOIN dbo.BuyerAddress ON dbo.Address.AddressID=dbo.
WHERE OwnerID = @pVendorID
ORDER BY OwnerID ASC, dbo.Address.LastModifiedDa
SELECT dbo.Contact.ContactID,
OwnerID,
ISNULL(Contact,'') AS 'Contact',
BuyerID
FROM dbo.Contact
LEFT JOIN dbo.BuyerContact ON dbo.Contact.ContactID=dbo.
WHERE OwnerID = @pVendorID
ORDER BY OwnerID ASC, dbo.Contact.LastModifiedDa
SELECT PhoneID AS 'PhoneID',
OwnerID AS 'ContactID',
Phone
FROM dbo.Phone
WHERE OwnerID IN
(SELECT dbo.Contact.ContactID
FROM dbo.Contact
LEFT JOIN dbo.BuyerContact ON dbo.Contact.ContactID=dbo.
WHERE OwnerID IN
(SELECT VENDOR_KEY FROM fnVendorRead () WHERE VENDOR_KEY = @pVendorID
))
AND PhoneTypeID = @Phone
ORDER BY 'ContactID' ASC, dbo.Phone.LastModifiedDate
SELECT PhoneID AS 'FaxID',
OwnerID AS 'ContactID',
Phone AS 'Fax'
FROM dbo.Phone
WHERE OwnerID IN
(SELECT dbo.Contact.ContactID
FROM dbo.Contact
LEFT JOIN dbo.BuyerContact ON dbo.Contact.ContactID=dbo.
WHERE OwnerID IN
(SELECT VENDOR_KEY FROM fnVendorRead () WHERE VENDOR_KEY = @pVendorID
))
AND PhoneTypeID = @Fax
ORDER BY 'ContactID' ASC, dbo.Phone.LastModifiedDate
SELECT PhoneID AS 'CellID',
OwnerID AS 'ContactID',
Phone AS 'Cell'
FROM dbo.Phone
WHERE OwnerID IN
(SELECT dbo.Contact.ContactID
FROM dbo.Contact
LEFT JOIN dbo.BuyerContact ON dbo.Contact.ContactID=dbo.
WHERE OwnerID IN
(SELECT VENDOR_KEY FROM fnVendorRead () WHERE VENDOR_KEY = @pVendorID
))
AND PhoneTypeID = @Cell
ORDER BY 'ContactID' ASC, dbo.Phone.LastModifiedDate
SELECT OnlineAddressID,
OwnerID AS 'ContactID',
OnlineAddress
FROM dbo.OnlineAddress
WHERE OwnerID IN
(SELECT dbo.Contact.ContactID
FROM dbo.Contact
LEFT JOIN dbo.BuyerContact ON dbo.Contact.ContactID=dbo.
WHERE OwnerID IN
(SELECT VENDOR_KEY FROM fnVendorRead () WHERE VENDOR_KEY = @pVendorID
))
ORDER BY 'ContactID' ASC, dbo.OnlineAddress.LastModi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.