4

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
  • 3
    Back in SQL 2000, *= was used to denote a left outer join, and =* was used to denote a right outer join. If you're looking to replace just those, that's how you'd do it. If you need something else, please update your question to be more specific. – Erik Darling Jan 26 '17 at 0:34
  • 3
    Start with PRINT @SQLCmd; which should at least make parsing the intent of the query a little easier to follow. – Aaron Bertrand Jan 26 '17 at 0:50
8

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 + ''''
  • 1
    Thanks for the incredible feedback.. this has helped me greatly and I can use this as a template for reference in the future. – Anna Trotta Jan 26 '17 at 16:22
-1

Looks like this to me:

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 + 
    'LEFT OUTER JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code' +
    'LEFT OUTER 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 + '.Retention_Code = RETENTION.Retention_Code ' +
    'WHERE ''' + @InvType + ''' = RESV.Item_Type ' +
        @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 + 
    'LEFT OUTER JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code' +
    'LEFT OUTER 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 + '.Retention_Code = RETENTION.Retention_Code ' +
    'WHERE ''' + @InvType + ''' = RESV.Item_Type ' +
    CASE LTRIM(@RetnType)
        WHEN '' THEN ''
        ELSE 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
    END

Although I can't tell if this bit:

''' + @InvType + ''' = RESV.Item_Type ' +

is supposed to be part of the join or where condition.

A few things to note:

  • This is highly prone to SQL Injection attack. The @TableName variable, for instance, could be used to do some nasty stuff, if a malicious user wanted to. (imagine passing in '; DROP TABLE Customers' or something similar).
  • There is probably a better way of doing this in terms of performance. See if you can put this into a stored procedure and use parameters for where statements
  • This is hard to debug, make it easier for the future by finding a better way! Stored procs/views etc... not building SQL on the fly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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