How do i crack this non-ansi code blob I've just started a new job about a month ago working for a software company that is really understaffed. I've been tasked with modernizing some of this old non-ansi SQL code and I'm having a particularly tough time trying to crack open this blerb. I'm recently out of school so I'm marginally familiar with modern SQL standards and from the research I've done the deprecated piece would relate to the non-ansi joins *= . I've tried pretty much every way I can think of altering this mess and I'm at a loss.
Thanks for your assistance.

Select @SQLCmd = 
'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS     Item_Number, Alternate_ID, Description1, ' +
    'Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, ' +
    'Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, ' + @TableName + '.Retention_Code, ' +
    'Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status ' +
'FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS ' +
'Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV ' +
'WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND ' +
    @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type ' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End
+ ' AND ' + @WhereCond +
' UNION ' + 
'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS Item_Number, Alternate_ID, Description1, ' +
    'Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, ' +
    'Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, ' + @TableName + '.Retention_Code, ' +
    'Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status ' +
'FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS ' +
'Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV ' +
'WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND ' +
    @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type ' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End
Consider these two simple tables:

CREATE TABLE dbo.t1
(
    t1_ID int NOT NULL
);

CREATE TABLE dbo.t2
(
    t2_ID int NOT NULL
);


"old style" JOINs used in SQL Server 2000 and earlier, that look like this:

SELECT *
FROM dbo.t1
    , dbo.t2
WHERE t1.t1_ID *= t2.t2_ID;


Are equivalent to an ANSI-style LEFT JOIN:

SELECT *
FROM dbo.t1 
    LEFT JOIN dbo.t2 ON t1.t1_ID = t2.t2_ID;


This:

SELECT *
FROM dbo.t1
    , dbo.t2
WHERE t1.t1_ID =* t2.t2_ID;


becomes:

SELECT *
FROM dbo.t1 
    RIGHT JOIN dbo.t2 ON t1.t1_ID = t2.t2_ID;


Although, I'm not a fan of using RIGHT JOIN, and would rewrite this to its equivalent LEFT JOIN, as:

SELECT *
FROM dbo.t2 
    LEFT JOIN dbo.t1 ON t2.t2_ID = t1.t1_ID;


The dynamic SQL in your question is complex enough that I'd first use PRINT to print out a sample of the generated code, then I'd use the above rules to convert the old style joins into ANSI-compliant ones.  

I've mocked-up the following from your code, in an attempt to simplify it:

DECLARE @SQLCmd nvarchar(max);
DECLARE @InvType nvarchar(100) = 'InvType';
DECLARE @TableName nvarchar(128) = 'TableName';
DECLARE @RetnType nvarchar(100) = 'RetnType';
DECLARE @WhereCond nvarchar(1000) = 'WhereCond';

Select @SQLCmd = 
'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS     Item_Number, Alternate_ID, Description1, 
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, 
    Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, ' + @TableName + '.Retention_Code, 
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status 
FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS 
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV 
WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND ' +
    @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type 
    ' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End
+ ' 
    AND ' + @WhereCond +
' UNION 
SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS Item_Number, Alternate_ID, Description1, 
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, 
    Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, ' + @TableName + '.Retention_Code, 
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status 
FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS 
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV 
WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND 
' + @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type 
' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End

PRINT @SQLCmd;


This results in:

SELECT 'InvType' AS Inv_Type, TableName_Number AS     Item_Number, Alternate_ID, Description1, 
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, 
    Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, TableName.Retention_Code, 
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status 
FROM TableName, RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS 
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV 
WHERE TableName.Retention_Code *= RETENTION.Retention_Code AND TableName_Number *= RESV.Item_Number AND 'InvType' = RESV.Item_Type 
    AND TableName.Retention_Type = 'RetnType' 
    AND WhereCond UNION 
SELECT 'InvType' AS Inv_Type, TableName_Number AS Item_Number, Alternate_ID, Description1, 
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code, 
    Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, TableName.Retention_Code, 
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status 
FROM TableName, RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS 
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV 
WHERE TableName.Retention_Code *= RETENTION.Retention_Code AND 
TableName_Number *= RESV.Item_Number AND 'InvType' = RESV.Item_Type 
AND TableName.Retention_Type = 'RetnType'


Which I'd reformat so that each column is on a separate line:

SELECT 'InvType' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status 
FROM TableName
    , RETENTION
    , (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status 
        FROM RESERVATION 
        GROUP BY Item_Type, Item_Number
        ) AS RESV 
WHERE TableName.Retention_Code *= RETENTION.Retention_Code 
    AND TableName_Number *= RESV.Item_Number 
    AND 'InvType' = RESV.Item_Type 
    AND TableName.Retention_Type = 'RetnType' 
    AND WhereCond 
UNION 
SELECT 'InvType' AS Inv_Type
    , TableName_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status 
FROM TableName
    , RETENTION
    , (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status 
        FROM RESERVATION 
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV 
WHERE TableName.Retention_Code *= RETENTION.Retention_Code 
    AND TableName_Number *= RESV.Item_Number 
    AND 'InvType' = RESV.Item_Type 
    AND TableName.Retention_Type = 'RetnType'


A bit tedious, but now we can see what the query actually does.

Converting the "old-style" joins into ANSI-style joins results in:

SELECT 'InvType' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status 
FROM TableName
    LEFT JOIN RETENTION ON TableName.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status 
        FROM RESERVATION 
        GROUP BY Item_Type, Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number 
WHERE 'InvType' = RESV.Item_Type 
    AND TableName.Retention_Type = 'RetnType' 
    AND WhereCond 
UNION 
SELECT 'InvType' AS Inv_Type
    , TableName_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status 
FROM TableName
    LEFT JOIN RETENTION ON TableName.Retention_Code = RETENTION.Retention_Code 
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status 
        FROM RESERVATION 
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number  
WHERE 'InvType' = RESV.Item_Type 
    AND TableName.Retention_Type = 'RetnType'


Folding that back into dynamic SQL results in:

DECLARE @SQLCmd nvarchar(max);
DECLARE @TableName nvarchar(128);
DECLARE @InvType nvarchar(100);
DECLARE @RetnType nvarchar(100);
DECLARE @WhereCond nvarchar(1000);

SET @SQLCmd = 'SELECT ''' + @InvType + ''' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status 
FROM ' + @TableName + '
    LEFT JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status 
        FROM RESERVATION 
        GROUP BY Item_Type, Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number 
WHERE ' + @InvType + ' = RESV.Item_Type 
    AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''' 
    AND ' + @WhereCond + '
UNION 
SELECT ''' + @InvType + ''' AS Inv_Type
    , ' + @TableName + '_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , ' + @TableName + '.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status 
FROM ' + @TableName + '
    LEFT JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code 
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status 
        FROM RESERVATION 
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV ON ' + @TableName + '_Number = RESV.Item_Number  
WHERE ''' + @InvType + ''' = RESV.Item_Type 
    AND TableName.Retention_Type = ''' + @RetnType + ''''