Appendix D: Errors To Be Solved For Dynamic SQL

All problems that may occur in static SQL code may, in fact, also occur in dynamic SQL. Static SQL code is automatically translated by the SSMA tool; all dynamic SQL code must be written manually. So, in theory, dynamic SQL involves more work, but in practice, dynamic SQL is relatively simple. In the standard version, the following problems were encountered, which were discussed in Appendix A, Appendix B and Appendix C.

1. Do not use double quotes to mark the beginning or end of a string; use single quotes instead. If the string contains a single quote, use two single quotes.

   Example:

   Old code:	"SELECT 'CountryCode' = CountryCode FROM T_Country"
   New code:	'SELECT ''CountryCode'' = CountryCode FROM T_Country'

2. It is no longer possible to pass on 'non-existing' parameters to procedures.
3. A parameter can be passed to a procedure no more than once.
4. Change DateDiff() calls to ssma_datediff() emulation function calls.
5. Change DatePart() calls to ssma_datepart() emulation function calls.
6. Replace the DateAdd(cwk, …) or DateAdd(calweekofyear, …) function call by the DateAdd(wk, …) function call and replace the DateAdd(cdw, …) or DateAdd(caldayofweek, …) function call by the DateAdd(dw, …) function call.
7. Instead of the DataLength() function for char, varchar and text fields, use the LEN() function.
8. Instead of the CharLength() function, use the LEN() function.

   Warning: The ASE LEN() function takes into account trailing spaces, but the MS SQL Server LEN() function does not. If you want to include trailing spaces, use the char_length_nvarchar() emulation function.

9. Use IS NULL and IS NOT NULL.

   Because the OLEDB and ODBC drivers themselves send a default connection string, the programming code used for comparisons with NULL should be as defensive as possible. The default is ANSI_NULLS = ON. Using a search and replace operation on the files, the following changes can be made:

   Old code	New code
   x = NULL	x IS NULL
   x <> NULL	x IS NOT NULL
   x != NULL	x IS NOT NULL
   NULL = x	x IS NULL
   NULL <> x	x IS NOT NULL
   NULL != x	x IS NOT NULL

   The first conversion (= NULL to IS NULL) cannot take place automatically. For the conversions <> NULL and != NULL, you can perform a search and replace operation in the files. This needs to be done first, because the = NULL pattern is also part of the !=NULL pattern. This method will result in fewer remaining items to check. In MS SQL, the NULL IS [NOT] x construct is not allowed and must be rewritten to x IS [NOT] NULL, which must also be done manually.

10. Change Char to NChar.
11. Change VarChar to NVarChar.
12. Change Text to NVarChar(max).
13. Change Image to VarBinary(max).
14. Change DateTime to DateTime2(3).
15. Change the Sybase outer join syntax (*= and =*) to the ANSI join syntax (LEFT OUTER JOIN and RIGHT OUTER JOIN).
16. Apply RTRIM to the right-hand argument of a LIKE operator.