Stored Procedure in Simple Terms With Sample

Your explanation is almost correct

What is different is I cant see place in procedure where TravelAgentID is assigned as you say

Also for TravelAgentID as NULL you're comparing the passed TravelAgentID value to PersonID which I'm not sure is the right way! If TravelAgent is not assigned then whats the purpose of doing comparison against PersonID? Doesnt make much sense unless you elaborate!

Also your above SP can be simplified to just this

CREATE PROCEDURE dbo.sp_get_BIaccessFoundation
    @OrgUnit NUMERIC(18,0), 
    @TravelAgentID NUMERIC(18,0) = NULL 
AS
DECLARE @procname varchar(30) 
    SELECT @procname = object_name(@@procid) 
    
            SELECT BIUsername 
            FROM Foundation..BIaccess 
            WHERE OrgUnit = @OrgUnit 
            AND (TravelAgentID = @TravelAgentID
            OR EXISTS (SELECT 1
            FROM Foundation..Person per 
            WHERE bia.OrgUnit = per.MasterOrgUnitID
            AND Per.PersonID = @TravelAgentID 
            )) 
      
      
        IF @@ROWCOUNT = 0
            BEGIN
                RAISERROR 101101 '%1!: No BI user exists for your calling parameters',@procname
                RETURN(-6)
            END
go

---

Please Mark This As Answer if it solved your issue

Please Mark This As Helpful if it helps to solve your issue

Visakh

----------------------------

My MSDN Page

My Personal Blog

My Facebook Page