User-46526262 posted
I am a beginner , i wrote one query for vendor ID . Actually It displays three type of output. but i need a single output with compare of three tables.
Pls kindly reply me
IF OBJECT_ID('SP_DELETE_VENDORID','P')IS NOT NULL
DROP PROCEDURE SP_DELETE_VENDORID;
GO
CREATE PROCEDURE SP_DELETE_VENDORID
@enclaveOID BIGINT
AS
BEGIN
DECLARE @pending_amt Numeric(9)
DECLARE @Date DATETIME
DECLARE @doj DATETIME
SELECT @pending_amt = pending_amt FROM tblVendorPayment WHERE EnclaveOID=@enclaveoid AND Status='A'
SELECT @Date= AssignedDate FROM tblAssignedVendor WHERE EnclaveOID=@enclaveoid AND Status='A'
SELECT @doj = doj FROM tblVendor WHERE EnclaveOID=@enclaveoid AND Status='A'
IF(@pending_amt IS NULL)
SELECT v.OID,v.supplier_id from tblVendor V
inner join tblVendorPayment vp on vp.VendorOID=vp.OID
WHERE v.EnclaveOID =@enclaveOID AND v.Status='A'
END
IF(@Date IS NOT NULL)
BEGIN
SELECT v.OID,v.supplier_id from tblVendor V
inner join tblAssignedVendor av on av.VendorOID=av.OID
WHERE v.EnclaveOID =@enclaveOID AND v.Status='A'
END
IF (@doj IS NOT NULL)
BEGIN
SELECT OID,supplier_id from tblVendor WHERE EnclaveOID=@enclaveOID AND Status='A' AND OID
NOT IN(SELECT VendorOID from tblAssignedVendor WHERE EnclaveOID=@enclaveOID AND Status='A'
UNION ALL
SELECT VendorOID FROM tblVendorPayment WHERE EnclaveOID =@enclaveOID AND Status='A')
END
GO
