Published 16 November 2017
Code style issues.
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.
It is recommended to specify alias via AS.
SQL uses the AS keyword to specify an alias. The assignment syntax is deprecated.
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 best to be consistent.
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.
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
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.
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.
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.
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.
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.
These are much easier to work with, are pretty secure, and they trigger fewer recompiles in the routines where they’re used than if you were to use temporary tables.
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.
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.