Stored procedure with output parameters in MSSQL

Question

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

• Billal BEGUERADJ: I think having at least the related entity relationship diagram would be helpful to get feedback
• surya: @BillalBegueradj I've simplified the code. Basically, I don't want to query the invoice table twice. Is there a way I can avoid this by using output parameters
• πάντα ῥεῖ: ... plus some detailing tags which SQL dialect is used (eg. postgres).

Answer 1

You're looking for a JOIN.

SELECT i.id, l.pid, l.sid 
FROM invoice i
JOIN log l ON i.pid = l.pid
WHERE i.batchid = @batchid;

Use EXPLAIN SELECT ... to verify the query plan is exploiting appropriate indexes.

Answer 2

If you want to optimize the query and use out parameters to return IDs please refer to below code:

CREATE OR ALTER PROCEDURE INVOICE_IDS
 @BATCHID INT,
 @ID INT OUT,
 @PID INT OUT,
 @SID INT OUT
AS
BEGIN
 SELECT @ID = I.ID, @PID = I.PID, @SID = L.SID
 FROM INVOICE AS I
 INNER JOIN LOG AS L ON I.PID = L.PID
 WHERE I.BATCHID = @BATCHID;
END

Adding join will avoid redundant process on Invoice table. Furthermore: if ID, PID, and SID will be unique for each BatchID, and will return single row, than its better to use out parameters, else using this approach is not a good idea in that case use approach as per above answer