Published 30 May 2017
Code style issues.
ST001 – Old-style join is used (...from table1,table2...)
Use ANSI-style joins via JOIN.
This form of SQL is no longer used. It is always better to specify the type of join you are using, INNER, LEFT OUTER, LEFT OUTER, FULL OUTER and CROSS. If you are only using INNER JOIN then that is a far bigger problem.
ST002 – Old-style column alias via EQUAL sign
It is recommended to specify alias via AS.
SQL uses the AS keyword to specify an alias. The assignment syntax is deprecated.
ST003 – Procedure body not enclosed with BEGIN...END
It is recommended to enclose a routine body in a BEGIN...END block.
The BEGIN …END block declaration is optional for stored procedure though it is mandatory for multi-line user-defined functions. To avoid confusion it is as well to be consistent.
ST004 – SQL-92 style cursor declaration is used
Use extended syntax instead.
You use the DECLARE CURSOR command to define the attributes of a T-SQL cursor. DECLARE CURSOR can accept the SQL-92 standard syntax and the SQL Server Extended syntax. Only the extended syntax supports the many T-SQL extensions to cursors.
ST005 – IF or ELSE without BEGIN...END block
Note: By default, this rule is set to "Ignore". To enable it, set it to "Warning" in the Rules dialog.
It is recommended to use BEGIN...END as a wrapper for a block of code in IF or ELSE statements.
Although, in theory you only need a block wrapper if more than one statement is used in an IF ELSE sequence It is easy to get statements wrong if you leave out the BEGIN…END
ST006 – Old-style TOP clause is used
It is recommended to use the new style TOP clause - TOP(n).
In order to limit the results of a query, you can specify the number you need by using the TOP clause. The TOP syntax is clearer with the parentheses, though they are syntactic sugar since only one parameter can be within parentheses.
ST007 – Cursor name is reused
More than one cursor has the same name.
Cursors are often thought to have the same scope as variables but this is not the case. It is a mistake to reuse the cursor name.
ST008 – Non-named parameter style used
Use named parameters when calling procedure (exec dbo.Procedure @Parameter1=value,@parameter2=...). Issue registered once per procedure call.
It is better to provide parameters by name, particularly during active development as otherwise, as procedures are improved, parameters have to be added at the end of the list rather than in logical order, and parameters cannot easily be deleted without causing several mysterious problems.
ST009 – Avoid using GOTO to improve readability
GOTO is perfectly legal but it becomes extraordinarily difficult to maintain code that uses GOTOs rather than conventional block structures. It is very rare to find any circumstances where a GOTO is necessary.
GOTO checks for specific labels can be ignored. You may, for example, have a corporate convention to use labels with standard names such as ‘Finish’, ‘ExitWithError’, ‘Cleanup’, and so on. In this instance, you can add the labels to ST009 Allowed Labels in the Rules / Customizable Rules dialog using standard regular expression syntax.
ST010 – Use alias for all table sources
Aliases are designed to improve readability, not to save typing.
You only need aliases where you are joining tables that, together with their schemas, have long names.
For consistency, it is often best to use aliases for every SQL expression that involves more than one table source.
There is no performance advantage to using aliases.
ST011 – Consider using table variable instead of temporary table
These are much easier to work with, and pretty secure, and they trigger fewer recompiles in the routines where they’re used than if you were to use temporary tables. Table variables require less locking resources as they are ‘private’ to the process that created them.
ST012 – Consider using temporary table instead of table variable
If you are doing more complex processing on temporary data or likely to use more than reasonably small amounts of data in them, then local temporary tables are likely to be a better choice than a table variable.
ST013 – Non-ANSI NOT_EQUAL operator used (!=)
Use ANSI-style NOT_EQUAL operator (<>).
The ‘!=’ symbol for not equal is not part of the SQL language. It is understood but only for the sake of backward-compatibility.