SELECT statement returning incorrect results when executed from stored procedure, but works ok when tested independently

Question

Any guess what could be wrong with the SELECT statement shown below; it returns null when tested from a stored procedure (no issues with the inputs), but the test passes when executed independently.

SELECT 
    @JunitSlugs = ISNULL(@JunitSlugs, '') + ',' + JunitSlug + '-' +
                  (SELECT LookupValue
                   FROM Lookup
                   WHERE LookupId = JunitTypeId)
FROM 
    JunitList
WHERE 
    JunitId IN (SELECT JunitId
                FROM CapsuleJunits
                WHERE CapsuleId = @CapsuleId)

I am not a SQL expert, but I know there can be a better way of writing above SQL statement. Please suggest.

BELOW COMPLETE PROC

ALTER PROCEDURE [dbo].[usp_CapsuleList_GetReadyToPub] (@xmlDoc XML = NULL)
AS
BEGIN
    BEGIN TRY
        SET NOCOUNT ON

        DECLARE @CapsuleId INT
        DECLARE @PublicationIds VARCHAR(128)
        DECLARE @JunitSlugs VARCHAR(MAX)

        IF @xmlDoc IS NOT NULL
        BEGIN
            SELECT @CapsuleId = c.value('(CapsuleId)[1]', 'INT')
                ,@PublicationIds = c.value('(PublicationIds)[1]', 'VARCHAR(128)')
            FROM @xmlDoc.nodes('MCapsuleList') AS T(C)
        END

        BEGIN
            SELECT @JunitSlugs = ISNULL(@JunitSlugs, '') + ',' + JunitSlug + '-' + (
                    SELECT LookupValue
                    FROM Lookup
                    WHERE LookupId = JunitTypeId
                    )
            FROM JunitList
            WHERE JunitId IN (
                    SELECT JunitId
                    FROM CapsuleJunits
                    WHERE CapsuleId = 211
                    )

            --SELECT @JunitSlugs = ISNULL(@JunitSlugs, '') + ',' + JunitSlug + '-' + (SELECT LookupValue FROM Lookup WHERE LookupId = JunitTypeId) FROM JunitList WHERE JunitId IN ( SELECT JunitId FROM CapsuleJunits WHERE CapsuleId = @CapsuleId)
            SELECT @JunitSlugs = ISNULL(@JunitSlugs, '') + ',' + jl.JunitSlug + '-' + lu.LookupValue
            FROM JunitList jl
            INNER JOIN Lookup lu ON jl.JunitTypeID = lu.LookupID
            WHERE jl.JunitId IN (
                    SELECT JunitId
                    FROM CapsuleJunits
                    WHERE CapsuleId = @CapsuleId
                    )

            SELECT (
                    SELECT CL.[CapsuleId]
                        ,[PublicationIds]
                        ,[CapsuleSlug] AS Slug
                        ,@JunitSlugs AS JunitIds
                        --,CreatedBy    
                        ,[Headline] AS HeadLine
                        --,[Excerpt]    
                        --,[Correction]           
                        ,CL.UpdatedOn AS UpdatedOn
                    FROM CapsuleList CL
                    --LEFT JOIN [PublishUrls] PU ON PU.ParentId = CL.CapsuleId    
                    WHERE CL.PublicationIds = @PublicationIds
                        AND CL.CapsuleStatusId = 53
                    ORDER BY CL.CapsuleId
                    FOR XML PATH('CapsuleList')
                        ,TYPE
                    )
            FOR XML PATH('CapsuleLists')
                ,ROOT('CapsuleListInfoByXml')
        END
    END TRY

    BEGIN CATCH
        DECLARE @ReturnedErrorID INT

        EXECUTE @ReturnedErrorID = dbo.usp_HandleException
    END CATCH

    SET NOCOUNT OFF
END

Answer 1

As far as changing the SQL statement, you could do this (using a variety of JOINS):

SELECT @JunitSlugs = ISNULL(@JunitSlugs, '') + ',' + jl.JunitSlug + '-' + lu.LookupValue
FROM JunitList jl
INNER JOIN Lookup lu ON jl.JunitTypeID = lu.LookupID
WHERE jl.JunitId IN (SELECT JunitId
                     FROM CapsuleJunits
                     WHERE CapsuleId = @CapsuleId)

---

In comments we've discussed that your SELECT outside of the stored procedure is returning value, so I've removed the part of my answer about checking for NULL. I've also removed a part about whether you are using an OUTPUT parameter in your stored procedure since you've recently posted what your stored procedure looks like. Possibly the question on the stored procedure should be its own separate question since it involves much more than just the initial query posted in the original question.