Code

APX1086 – Check syntax errors

Description:

This rule evaluates the T-SQL script for any syntax error, by parsing the T-SQL statement and show any errors.

It is recommended to check the documentation for each T-SQL statement in order to use it in the query properly.

For more information visit

https://www.tsql.info/

Example script:

Message:

Incorrect syntax near ‘*’

APX1095 – GOTO Usage

Description:

This rule evaluates the T-SQL script for using GOTO statements.

The excessive GOTO use considered as poor programming practice that may lead to unmaintainable and unreadable code when the code grows long.

User better alternative statements to control the execution path.

For more information visit

https://www.quora.com/Why-is-the-goto-statement-in-C-advised-to-avoid

Example script:

Message:

GOTO statement detected

APX1112 – Semi-colons statement terminator

Description:

This rule evaluates the T-SQL script for missing semi-colons at the end of each statement.

Although technically statement not terminated with semi-colons, terminating statements with semi-colons improves code format, layout, and readability and as such is considered a best practice.

For more information visit

https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016

Example script:

Message:

The statement ‘SELECT * FROM FirstTable’ is not terminated with semi-colon

APX1113 – Uninitialized variable

Description:

This rule evaluates the T-SQL script for using the variables before they are initialized.

Using uninitialized variables will result with incorrect output.

You should initialize all variables before the first use, as T-SQL guarantees only the newly declared variables will be initialized to NULL.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/variables-transact-sql?view=sql-server-2017

Example script:

Message:

The variable @Var2 is used but not initialized

APX1114 – Unused parameter

Description:

This rule evaluates the T-SQL script for having unused parameters.

Unused parameters are considered “dead code” that contribute to maintenance and readability problems.

It is considered a best practice to keep code clean of any unused elements.

For more information visit

https://solutioncenter.apexsql.com/how-to-clean-up-sql-database-code-by-removing-unused-parameters/

Example script:

Message:

Parameter ‘@FourthAddressLine NVARCHAR(60) = NULL’ is not used

APX1123 – SET NOCOUNT OFF Usage

Description:

This rule evaluates the T-SQL script for using SET NOCOUNT OFF in functions, stored procedures and triggers unless row affected output is needed.

Setting the NOCOUNT option to ON helps significantly in enhancing the performance of stored procedures that contain loops and many statements that don’t return too much actual data, by reducing the network traffic caused by writing such type of messages.

For more information visit

https://www.sqlshack.com/sql-server-set-options-affect-query-result-set-concat_null_yields_null-set-numeric_roundabort-set-quoted_identifier-set-nocount-set-xact_abort/

Example script:

Message:

SET NOCOUNT is off

APX1131 – Un-closed cursor

Description:

This rule evaluates the T-SQL script for leaving the local cursors opened.

Any opened local cursors should be explicitly closed.

When you close an opened cursor, the current result set will be released, and any cursor locks held on the rows on which the cursor is positioned will be freed. On the other hand, CLOSE will leave the data structures available for reopening again, without being able to fetch it until the cursor is reopened.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/functions/cursor-status-transact-sql?view=sql-server-2017

Example script:

Message:

Local cursor ‘MyFirstFailedCursor’ not closed

APX1132 – Cursor not deallocated

Description

This rule evaluates the T-SQL script for NOT deallocating the local cursor explicitly.

When the cursor reference is deallocated, all cursor references are removed, and the data structures comprising the cursor are released. When a cursor name is associated with a cursor, the name cannot be used for another cursor of the same scope (global or local) until this cursor has been deallocated.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/deallocate-transact-sql?view=sql-server-2017

Example script:

Message:

The cursor, ‘MyThirdFailedCursor’ is not explicitly deallocated

APX1133 – RAISERROR with severity above 18 missing WITH LOG clause

Description:

This rule evaluates the T-SQL script for writing a RAISERROR statements with a severity above 18 that lack a WITH LOG clause.

For severity levels from 19 through 25, the WITH LOG option is required.

The WITH LOG option is used to Log the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql

Example script:

Message:

RAISEERROR statement with severity above > 18 and doesn’t contain a WITH LOG clause

APX1136 – Direct use of Date-Time functions

Description:

This rule evaluates the T-SQL script for having micro-differences during the running of code.

This is due to the fact that the Date-time functions are considered as non-deterministic functions.

This can be avoided by obtaining date-time information as a variable at the beginning of a procedure.

For more information visit

https://www.sqlshack.com/sql-convert-date-functions-and-formats/

Example script:

Message:

Direct use of Date-Time functions detected vs storing in a variable

APX1139 – WAITFOR delay statement

Description:

This rule evaluates the T-SQL script for using WAITFOR statement with DELAY or TIME inside a programmability object.

Generally, such statements should be avoided except for very specific cases.

The WAITFOR command does consume a small number of CPU cycles so it is recommended not to use it excessively.

For more information visit

https://www.sqlshack.com/sql-server-wait-type-waitfor/

Example script:

Message:

WAITFOR statement detected

APX1154 – Unpaired sp_xml_preparedocument

Description:

This rule evaluates the T-SQL script for calling the sp_xml_preparedocument procedure without being paired with and followed by an sp_xml_removedocument call.

The MSXML parser uses one-eighth the total memory available for SQL Server.

To avoid running out of memory, run sp_xml_removedocument to free up the memory.

For more information visit

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-xml-preparedocument-transact-sql

Example script:

Message:

The XML document, @handle’*’, was opened but not closed

APX1162 – Cyclomatic complexity

Description:

This rule evaluates the T-SQL script for having complex or too large codes, by measuring cyclomatic complexity.

The code complexity is measured by the number of control flow statements e.g. IF

Complex code is unreadable and hard to be maintained.

For more information visit

https://www.geeksforgeeks.org/cyclomatic-complexity/

Example script:

Message:

Statement complexity of 25, exceeds the allowed threshold

APX1191 – Reserved word used as identifier

Description:

This rule evaluates the T-SQL script for using SQL Server reserved words as identifiers.

Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, you can do this only by using delimited identifiers.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-2017

Example script:

Message:

The reserved word, ‘<word>’, is used as an identifier

APX1206 – Invalid cursor operation

Description:

This rule checks the violation for any cursor operations like OPEN, FETCH, CLOSE, DEALLOCATE without a declared cursor.

Declaring a cursor is a pre-requisite for any of these operations.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql?view=sql-server-2017

Example script:

Message:

OPEN, CLOSE, FETCH or DELLOCATION clause without a declared cursor

APX1207 – Missing SET NOCOUNT ON before Unatteended DML execution

Description:

This rule evaluates the T-SQL script for missing the SET NOCOUNT ON option before executing the DML statement.

Setting NOCOUNT ON prevents viewing messages about the number of rows affected, which is unnecessary during unattended execution.

For more information visit

https://www.sqlshack.com/sql-server-set-options-affect-query-result-set-concat_null_yields_null-set-numeric_roundabort-set-quoted_identifier-set-nocount-set-xact_abort/

Example script:

Message:

No SET NOCOUNT ON

APX1209 – INSERT EXEC statement

Description:

This rule evaluates the T-SQL script for using INSERT EXEC that may result in the error “An INSERT EXEC statement cannot be nested”.

Avoid using nested INSERT EXEC statement where possible.

For more information visit

http://www.sommarskog.se/share_data.html#INSERTEXEC

Example script:

Message:

INSERT EXEC statement detected

APX1212 – Printing from a trigger

Description:

This rule evaluates the T-SQL script for printing results inside triggers.

Generally, printing inside triggers has no purpose and should be avoided as the triggers aren’t called explicitly, along with the unnecessary network traffic.

For more information visit

https://www.sqlshack.com/triggers-in-sql-server/

Example script:

Message:

PRINT statement detected inside this trigger

APX1220 – No TRY…CATCH

Description:

This rule evaluates the T-SQL script for NOT encapsulating all statements between the BEGIN and COMMIT/ROLLBACK statements in a TRY…CATCH block.

It is recommended to encapsulate your transaction in a TRY…CATCH block to end up with a safe-fail in the case of unexpected errors.

For more information visit

https://www.sqlshack.com/how-to-implement-error-handling-in-sql-server/

Example script:

Message:

BEGIN or COMMIT/ROLLBACK statement is not encapsulated in a TRY CATCH statement

APX1233 – Undocumented stored procedure usage

Description:

This rule evaluates the T-SQL script for using undocumented procedures.

It is recommended to replace the undocumented stored procedures with the documented ones.

Undocumented SQL Server Stored procedures are more likely to change and/or be deprecated.

For more information visit

https://social.technet.microsoft.com/wiki/contents/articles/16975.aspx

Example script:

Message:

Use of an undocumented procedure, EXEC sp_blockcnt;, detected

APX1235 – Non-recommended settings

Description:

This rule evaluates the T-SQL script for changing the value of certain options e.g. ARITHABORT.

These changes are not recommended as it will cause the statement to be recompiled on every execution.

Recommended options are:

  • ARITHABORT ON
  • ANSI_NULL_DFLT_ON ON
  • ANSI_PADDING ON
  • ANSI_WARNINGS ON
  • CONCAT_NULL_YIELDS_NULL ON
  • NUMERIC_ROUNDABOUT OFF

For more information visit

https://www.sqlshack.com/sql-server-set-options-affect-query-result-set-ansi_nulls-set-ansi_padding-set-ansi_warnings-set-arithabort/

Example script:

Message:

A recommended setting. SET ANSI_NULLS ON;, is changed in the statement

APX1241 – Cannot roll back THROW

Description:

This rule evaluates the T-SQL script for writing a THROW statement without a preceding statement terminator.

This may result in the error “Cannot roll back THROW. No transaction or savepoint of that name was found.”

A statement terminator should be used with the ROLLBACK statement to avoid that error.

For more information visit

https://www.sqlshack.com/how-to-implement-error-handling-in-sql-server/

Example script:

Message:

THROW statement will result in the error “Cannot roll back THROW”

APX1281 – Permission is granted in procedure body

Description:

This rule evaluates the T-SQL script for missing GO from the GRANT statement.

If GO is missing, the GRANT statement will be included as part of the procedure itself, essentially granting itself permission.

Use GO to close the procedure body and before executing the GRANT statement.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-2017

Example script:

Message:

Permission is granted in procedure body

APX1284 – Duplicate Cursor name

Description:

This rule evaluates the T-SQL script for declaring more than one cursor with the same name.

You cannot create more than one cursor with the same name before deallocating the first cursor.

Consider deallocating the cursor before using the same name again, or simply use a unique name for each cursor.

For more information visit

https://www.sqlshack.com/sql-server-cursor-tutorial/

Example script:

Message:

Cursor name, MyFailedCursor, is already in use

APX1285 – Cursor with no specified scope

Description:

This rule evaluates the T-SQL script for NOT declaring explicitly the cursor scope as LOCAL or GLOBAL.

It is better to define the scope of the cursor as LOCAL or GLOBAL explicitly.

If the cursor scope is not specified, the default value will be controlled by the setting of the default to the local cursor database option.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql

Example script:

Message:

The cursor scope, MySecondFailedCursor, is not specified

APX1287 – Hardcoded database name

Description:

This rule evaluates the T-SQL script for using hardcoded database names.

Such code is considered “brittle” code, that would break if the database name is changed.

Don’t use the database name unless you absolutely have to.

You can use SQL Server Synonyms to limit the naming dependency.

For more information visit

https://docs.microsoft.com/en-us/sql/relational-databases/synonyms/synonyms-database-engine?view=sql-server-2017

Example script:

Message:

A hardcoded, database name, <database_name>, is used

APX1288 – Statement outside of begin..end block

Description:

This rule evaluates the T-SQL script for mistakenly writing a statement outside the begin..end clause, such as including GO after a statement inside the begin..end clause.

The GO statement should be the last statement in any CREATE or ALTER statement.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-end-transact-sql?view=sql-server-2017

Example script:

Message:

A statement exists outside the begin..end block in procedure, pro_BeginEndBlockFirstFailedEx

APX1308 – Trigger should be enabled

Description:

This rule evaluates the T-SQL script for having disabled triggers.

It is recommended to keep the trigger enabled if its functionality is required.

If the trigger is disabled, some changes will not be recorded.

For more information visit

https://www.sqlshack.com/triggers-in-sql-server/

Example script:

Message:

Trigger(s) is disabled, consider enabling it

SQLShack

SQLShack

SQL Shack was created as a community service from ApexSQL, with the goal to share SQL Server knowledge through articles written by SQL Server professionals and community members
SQLShack

Latest posts by SQLShack (see all)