I want a stored procedure that retrieves ID and PID from the invoice table with batchid as input parameter and based on the retrieved PID I want to retrieve sid from the log table.
In total I want to retrieve 3 parameters - ID, PID and SID
I have written SP below but I think this can be optimized.Is there a way I can write this SP in MSSQL so that I don't have to query the invoice table twice.
CREATE OR ALTER PROCEDURE INVOICE_IDS(@BATCHID INT)
AS
BEGIN
SELECT ID, PID
FROM INVOICE where BATCHID =@BATCHID;
SELECT SID FROM LOG WHERE PID in ( SELECT PID
FROM INVOICE where BATCHID =@BATCHID );
END