For some reason my Stored Procedure returns nothing

Question

I am creating a stored procedure that returns data from many tables, so it has several chunks to it that finally result in one temp table. The only issue is it is not producing anything. All of the separate chunks seem to work, but as a whole it doesn't.

I feel like I may be missing something small, so your input is appreciated.

@ref_num    VARCHAR(20)

AS
BEGIN
    SET NOCOUNT ON;

BEGIN TRY

    DECLARE @clKey INT
    DECLARE @clName VARCHAR(50)
    SET @clKey = (SELECT ClearingBroker FROM DocumentRecieved WHERE ref_num = @ref_num)
    SET @clName = (SELECT EB_Name FROM EB_Company WHERE EB_dtc_num = @clKey)

    SELECT
                a.ref_num
                ,b.DateSent
                ,b.Document as 'FormName'
                ,b.ClearingBroker as 'ClearingBrokerID'
                ,b.ExecutingBroker as 'ExecutingBrokerID'
                ,c.EB_Name
                ,a.AccountName
    INTO        
                #Temp1
    FROM
                EB_Company c JOIN PBIN_records a ON c.EB_dtc_num = a.eb_key 
                             JOIN DocumentRecieved b ON a.ref_num = b.ref_num 
    WHERE
                a.ref_num = @ref_num

--================================================================================================

    CREATE TABLE 
                #Temp2 (
                            ClearingBroker  VARCHAR(50)
                        )

--================================================================================================

    INSERT INTO
                #Temp2  (
                            ClearingBroker
                        )

    VALUES
                        (
                            @clName
                        )

--================================================================================================                      

    SELECT
            fName + ' ' + lName as 'SentBy'
    INTO
            #Temp3
    FROM
            Master_Contacts JOIN PBIN_records ON PBIN_records.eb_contact_key = Master_Contacts.contactID
    WHERE
            PBIN_records.ref_num = @ref_num

--================================================================================================

    SELECT
            #Temp1.ref_num,
            #Temp1.FormName,
            #Temp1.DateSent,
            #Temp1.ClearingBrokerID,
            #Temp2.ClearingBroker,
            #Temp1.ExecutingBrokerID,
            #Temp1.EB_Name,
            #Temp1.AccountName,
            #Temp3.SentBy
    INTO
            #Temp4
    FROM
            #Temp1, #Temp2, #Temp3

--================================================================================================

    SELECT
            *
    FROM
            #Temp4

--================================================================================================

END TRY

EDIT: It appears since one of my tables has no records at the moment it ruins the entire process because my final SELECT INTO is a cartesian product between my first three temp tables.

Answer 1

Question: Do you really want a Cartesian product between #Temp1, #Temp2, #Temp3 ? I'm asking because FROM #Temp1, #Temp2, #Temp3 represents a Cartesian product.

One of the possible reasons is that one of those three tables has no rows.

Example: following query

SELECT  *
FROM    (SELECT 1 UNION ALL SELECT 2) AS TableA(ID)
CROSS JOIN (SELECT 1 WHERE 1=0) AS TableB(ID)

outputs 0 rows

ID          ID
----------- -----------

(0 row(s) affected)