3
\$\begingroup\$

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
\$\endgroup\$
3
  • \$\begingroup\$ I think having at least the related entity relationship diagram would be helpful to get feedback \$\endgroup\$ Commented May 17 at 9:22
  • \$\begingroup\$ @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 \$\endgroup\$
    – surya
    Commented May 17 at 9:49
  • \$\begingroup\$ ... plus some detailing tags which SQL dialect is used (eg. postgres). \$\endgroup\$ Commented May 17 at 9:50

2 Answers 2

2
\$\begingroup\$

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.

\$\endgroup\$
2
\$\begingroup\$

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

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.