HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How do i crack this non-ansi code blob

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sql-standardsqlsql-serverstackoverflowdba
thisnoncrackansihowcodeblob

Problem

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_

Solution

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

Code Snippets

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

CREATE TABLE dbo.t2
(
    t2_ID int NOT NULL
);
SELECT *
FROM dbo.t1
    , dbo.t2
WHERE t1.t1_ID *= t2.t2_ID;
SELECT *
FROM dbo.t1 
    LEFT JOIN dbo.t2 ON t1.t1_ID = t2.t2_ID;
SELECT *
FROM dbo.t1
    , dbo.t2
WHERE t1.t1_ID =* t2.t2_ID;
SELECT *
FROM dbo.t1 
    RIGHT JOIN dbo.t2 ON t1.t1_ID = t2.t2_ID;

Context

StackExchange Database Administrators Q#162278, answer score: 8

Revisions (0)

No revisions yet.