0

We have about 150 old style queries and views that use the *= and =* type ANSI92? join. Does anybody know of a tool / method or script that could help with the conversion or do we have to just slog through all 150 of them.

Thanks

Select  PapersSent,
DateSent,
Code,
ActionDate,
ClientAction,
ClientContactRef,
PublishAppraisal,
PublishCV,
SponsorContactREF,
MeetingNotes,
InternalNotes,
Contact_AdminAction,
MeetingLocation
from    tblMeetingNotes a,
    tblPapersOptions b,
    tblContactLog c 

where   a.CREF=@CREF and 
    a.CLID=@CLID AND 
    Isnull(PapersSent,0)*=Value AND
    a.CREF*=c.CREF AND
    a.CLID*=c.Contact_ID
Shannon Severance
  • 18,025
  • 3
  • 46
  • 67
Terry Wells
  • 73
  • 1
  • 5
  • 5
    "ANSI92? join" \*= and =\* are not ANSI 92. ANSI SQL 92 introduced the wordy join syntax like `inner join` and `left outer join`. – Shannon Severance Mar 15 '11 at 17:27
  • 2
    Any special reason you need to convert them? Why not just leave them as-is? – Ben Mar 19 '11 at 13:49
  • @Ben - The syntax is deprecated and according to this answer [doesn't always return the correct results](http://stackoverflow.com/questions/983862/sql-server-operator/983955#983955). – Martin Smith Mar 21 '11 at 18:29
  • @Ben - direct quote from Books Online in SQL Server 2000 "In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way." This means these joins should never be used and should not have been used even as long ago as eleven years ago as it is not guaranteed to perform as specified. If you have any of this code, you need to fix it. – HLGEM Mar 21 '11 at 18:43
  • "In some cases". If you don't have those cases you don't have a problem. Deprecated means "don't make any more", not "throw away the old ones". The interpretation of the operator hasn't changed since then. I don't recommend anyone to change debugged, battle tested code just because it is deprecated. – Ben Mar 21 '11 at 21:06

3 Answers3

2

This probably isn't what you were hoping to hear, but this type of tool doesn't exist. There are situations where an old style JOIN won't cleanly convert to the SQL-92 style, causing the query to give different results, or even requiring the query to be re-written.

Even if there were a tool to automatically convert the joins, you would still need to test every query to make sure that it converted how you wanted it to, creating probably just as much work as it would have been to do it by hand.

Erland Sommarskog has a good step-by-step process on how you would quickly convert the old style joins to SQL-92: http://www.sommarskog.se/Become-an-ANSI-star.doc

Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
1

Before you convert, I would definitely see about setting up some kind of testing framework so you can compare the results.

This will be easiest if all these are views, or if you can get the output into tables.

At that point you can use things like EXCEPT to ensure that all rows match.

In the past, I've code generated table comparisons using stored procs which take the tables/views and generate the comparisons. Even including numeric/percentage thresholds for amount differences where one set has had awkward rounding problems - like banker's rounding (Teradata) or IEEE floating point-based rounding (WebFocus).

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
0

You could script the database and use search and replace to change the bulk of them and manually inspect the more difficult cases. Be sure to test all the queries thoroughly in case the output has changed, as mfredrickson pointed out.

To help with the search, although not strictly necessary if you script the database, download Redgate's SQL Search (it's free) to help you find all the instances. Even if you don't use it for this task it's handy to have.

Tony
  • 9,672
  • 3
  • 47
  • 75