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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT * from FirstTable; SELECT * frm SecondTable; SELECT * form ThirdTable; DELETE from FourthTable wher id > 0; SELECT * from FifthTable; SELECT q from SixthTable; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
DECLARE @cnt INT; SET @cnt = 1; WHILE @cnt < 10 BEGIN SELECT @cnt; SET @cnt = @cnt + 1; IF @cnt = 3 GOTO FirstRoute; IF @cnt = 7 GOTO SecondRoute; END; FirstRoute: SELECT * FROM FirstTable; GOTO ThirdRoute; SecondRoute: SELECT * FROM SecondTable; ThirdRoute: SELECT * FROM ThirdTable; GO DECLARE @cnt INT; SET @cnt = 1; WHILE @cnt < 10 BEGIN SELECT @cnt; SET @cnt = @cnt + 1; IF @cnt = 4 BEGIN SELECT * FROM FirstTable; END; IF @cnt = 5 BEGIN SELECT * FROM ThirdTable; END; END; GO CREATE PROCEDURE proc_GoToFirstFailedEx AS BEGIN DECLARE @cnt INT; SET @cnt = 1; WHILE @cnt < 10 BEGIN SELECT @cnt; SET @cnt = @cnt + 1; IF @cnt = 4 GOTO FirstRoute; IF @cnt = 5 GOTO SecondRoute; END; FirstRoute: SELECT * FROM FirstTable; GOTO ThirdRoute; label2_1: SELECT * FROM SecondTable; label3_1: SELECT * FROM ThirdTable; END; GO CREATE PROCEDURE proc_GoToSecondFailedEx AS BEGIN DECLARE @cnt INT; SET @cnt = 1; WHILE @cnt < 10 BEGIN SELECT @cnt; SET @cnt = @cnt + 1; IF @cnt = 4 BEGIN SELECT * FROM FirstTable; SELECT * FROM ThirdTable; END; IF @cnt = 5 BEGIN SELECT * FROM SecondTable; SELECT * FROM ThirdTable; END; END; END; |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT * FROM FirstTable SELECT * FROM SecondTable; SELECT * FROM ThirdTable; SELECT * FROM FourthTable SELECT * FROM FifthTable SELECT * FROM SixthTable; SELECT * FROM SeventhTable; DELETE FROM EightthTable; UPDATE NinethTable SET val = 0; |
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
Example script:
|
1 2 3 4 5 6 7 8 |
DECLARE @Var1 AS INT, @Var2 AS INT, @Var3 AS INT; SET @Var1 = 1000; IF(@Var2 > 10 AND @Var2 < 100 AND @Var3 > 50) BEGIN SET @Var1 = 2000; END; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROCEDURE proc_UnusedParam @City NVARCHAR(30) = NULL, @FirstAddressLine NVARCHAR(60) = NULL, @SecondAddressLine NVARCHAR(60) = NULL, @ThirdAddressLine NVARCHAR(60) = NULL, @FourthAddressLine NVARCHAR(60) = NULL AS SELECT * FROM Persons WHERE City = @City AND AddressLine1 LIKE '%' + @FirstAddressLine + '%' AND StrField = @ThirdAddressLine; |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
CREATE PROCEDURE proc_NOCOUNTFirstFailedEx AS BEGIN DECLARE @cnt INT; SET @cnt = 1; WHILE @cnt < 10 BEGIN SELECT @cnt; SET @cnt = @cnt + 1; IF @cnt = 4 GOTO FirstRoute; IF @cnt = 5 GOTO SecondRoute; END; FirstRoute: SELECT * FROM FirstTable; GOTO ThirdRoute; SecondRoute: SELECT * FROM SecondTable; ThirdRoute: SELECT * FROM ThirdTable; END; GO CREATE PROCEDURE proc_NOCOUNTSecondFailedEx AS BEGIN DECLARE @cnt INT; SET @cnt = 1; WHILE @cnt < 10 BEGIN SELECT @cnt; SET @cnt = @cnt + 1; IF @cnt = 4 GOTO FirstRoute; IF @cnt = 5 GOTO SecondRoute; END; FirstRoute: SELECT * FROM FirstTable; GOTO ThirdRoute; SecondRoute: SELECT * FROM FirstTable; ThirdRoute: SELECT * FROM FirstTable; END; GO CREATE PROC proc_NOCOUNTFirstGoodEx AS SET NOCOUNT ON; SELECT * FROM dbo.FirstTable; GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
CREATE PROCEDURE proc_UnclosedCursorFirstFailedEx AS BEGIN DECLARE MyFirstFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MyFirstFailedCursor; FETCH NEXT FROM MyFirstFailedCursor; END; GO CREATE PROCEDURE proc_UnclosedCursorSecondFailedEx AS BEGIN DECLARE MySecondFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MySecondFailedCursor; FETCH NEXT FROM MySecondFailedCursor; END; GO CREATE PROCEDURE proc_UnclosedCursorFirstGoodEx AS BEGIN DECLARE MyFirstGoodCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MyFirstGoodCursor; FETCH NEXT FROM MyFirstGoodCursor; CLOSE MyFirstGoodCursor; DEALLOCATE MyFirstGoodCursor; END; GO CREATE PROCEDURE proc_UnclosedCursorSecondGoodEx AS BEGIN DECLARE MySecondGoodCursor CURSOR FOR SELECT * FROM SecondTable; OPEN MySecondGoodCursor; FETCH NEXT FROM MySecondGoodCursor; CLOSE MySecondGoodCursor; DEALLOCATE MySecondGoodCursor; END; GO |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
CREATE PROCEDURE proc_NotDeallocatedCursorFirstFailedEx AS BEGIN DECLARE MyFirstFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MyFirstFailedCursor; FETCH NEXT FROM MyFirstFailedCursor; END; GO CREATE PROCEDURE proc_NotDeallocatedCursorSecondFailedEx AS BEGIN DECLARE MySecondFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MySecondFailedCursor; FETCH NEXT FROM MySecondFailedCursor; END; GO CREATE PROCEDURE proc_NotDeallocatedCursorThirdFailedEx AS BEGIN DECLARE MyThirdFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MyThirdFailedCursor; FETCH NEXT FROM MyThirdFailedCursor; CLOSE MyThirdFailedCursor; END; GO CREATE PROCEDURE proc_NotDeallocatedCursorFirstGoodEx AS BEGIN DECLARE MyFirstGoodCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MyFirstGoodCursor; FETCH NEXT FROM MyFirstGoodCursor; CLOSE MyFirstGoodCursor; DEALLOCATE MyFirstGoodCursor; END; GO CREATE PROCEDURE proc_NotDeallocatedCursorSecondGoodEx AS BEGIN DECLARE MySecondGoodCursor CURSOR FOR SELECT * FROM SecondTable; OPEN MySecondGoodCursor; FETCH NEXT FROM MySecondGoodCursor; CLOSE MySecondGoodCursor; DEALLOCATE MySecondGoodCursor; END; GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
CREATE PROCEDURE proc_RAISERRORMissWithLogFirstFailedEx AS BEGIN RAISERROR(N'Error.', 20, 1); END; GO CREATE PROCEDURE proc_RAISERRORMissWithLogSecondFailedEx AS BEGIN RAISERROR(N'Error.', 21, 1); END; GO CREATE PROCEDURE proc_RAISERRORMissWithLogThirdFailedEx AS BEGIN RAISERROR(N'Error message %s %d.', 25, 1, N'number', 5); END; GO CREATE PROCEDURE proc_RAISERRORMissWithLogFirstGoodEx AS BEGIN RAISERROR(N'Error.', 10, 1); END; GO CREATE PROCEDURE proc_RAISERRORMissWithLogSecondGoodEx AS BEGIN RAISERROR(N'Error.', 30, 1) WITH LOG; END; GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
CREATE PROCEDURE proc_DirectDateTimeFunFirstFailedEx AS BEGIN SELECT SYSDATETIME(), CURRENT_TIMESTAMP; END; GO CREATE PROCEDURE proc_DirectDateTimeFunSecondFailedEx AS BEGIN SELECT SYSDATETIME(), SYSDATETIMEOFFSET(), SYSUTCDATETIME(), CURRENT_TIMESTAMP, GETDATE(), GETUTCDATE(); SELECT CONVERT(TIME, SYSDATETIME()), CONVERT(TIME, CURRENT_TIMESTAMP), CONVERT(TIME, GETDATE()); END; GO CREATE PROCEDURE proc_DirectDateTimeFunThirdFailedEx AS BEGIN SELECT SYSDATETIME(), CURRENT_TIMESTAMP, GETDATE(); END; GO CREATE PROCEDURE procDirectDateTimeFunFirstGoodEx AS BEGIN SELECT DATEPART(MONTH, GETUTCDATE()); END; GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
CREATE PROCEDURE proc_WAITFORFirstFailedEx AS BEGIN WAITFOR TIME '10:00'; DELETE FROM FirstTable; END; GO CREATE PROCEDURE proc_WAITFORSecondFailedEx AS BEGIN WAITFOR DELAY '01:00'; DELETE FROM FirstTable; WAITFOR DELAY '02:00'; DELETE FROM SecondTable; END; GO CREATE PROCEDURE proc_WAITFORThirdFailedEx AS BEGIN DECLARE @dellay CHAR(8)= '00:10:00'; WAITFOR DELAY @dellay; DELETE FROM FirstTable; END; GO CREATE PROC proc_WAITFORFirstGoodEx AS SET NOCOUNT ON; SELECT * FROM dbo.FirstTable; SET NOCOUNT OFF; GO |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
CREATE PROCEDURE proc_XMLPairedFirstFailedEx AS BEGIN DECLARE @handle1 INT; EXEC sp_xml_preparedocument @handle1 OUTPUT; END; GO CREATE PROCEDURE proc_XMLPairedSecondFailedEx AS BEGIN DECLARE @handle2 INT; DECLARE @xmlval VARCHAR(256); SET @xmlval = '<ROOT><resultset><title>ApexSQL Enforce Report</title></resultset></ROOT>'; EXEC sp_xml_preparedocument @handle2 OUTPUT, @xmlval; END; GO CREATE PROCEDURE proc_XMLPairedThirdFailedEx AS BEGIN DECLARE @handle3 INT; DECLARE @xmlval VARCHAR(256); SET @xmlval = '<ROOT><resultset><title>ApexSQL Enforce Report</title></resultset></ROOT>'; EXEC sp_xml_preparedocument @handle3 OUTPUT, @xmlval, '<ROOT xmlns:abc="urn:AxEnforce">'; END; GO CREATE PROCEDURE proc_XMLPairedFirstGoodEx AS BEGIN DECLARE @handle4 INT; EXEC sp_xml_preparedocument @handle4 OUTPUT; EXEC sp_xml_removedocument @handle4; END; GO CREATE PROCEDURE proc_XMLPairedSecondGoodEx AS BEGIN DECLARE @handle5 INT; DECLARE @xmlval VARCHAR(256); SET @xmlval = '<ROOT><resultset><title>ApexSQL Enforce Report</title></resultset></ROOT>'; EXEC sp_xml_preparedocument @handle5 OUTPUT, @xmlval; EXEC sp_xml_removedocument @handle5; END; GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
CREATE PROCEDURE proc_CyclomaticComplexFirstFailedEx AS BEGIN UPDATE FirstTable set col1=12 where col2 = 10; IF @@ROWCOUNT = 0 BEGIN UPDATE FirstTable set col1=25 where col2 = 100; IF @@ROWCOUNT = 0 BEGIN UPDATE FirstTable set col1=50 where col2 = 1000; IF @@ROWCOUNT = 0 BEGIN UPDATE FirstTable set col1=100 where col2 = 10000; END END END select * from FirstTable; END; GO CREATE PROCEDURE proc_CyclomaticComplexSecondFailedEx AS BEGIN DECLARE @handle INT; DECLARE @xmlval VARCHAR(256); SET @xmlval = '<ROOT><resultset><title>ApexSQL Enforce Report</title></resultset></ROOT>'; EXEC sp_xml_preparedocument @handle OUTPUT, @xmlval; EXEC sp_xml_removedocument @handle; END; GO CREATE PROCEDURE proc_CyclomaticComplexFirstGoodEx AS BEGIN SELECT * FROM SecondTable; END; GO CREATE PROCEDURE proc_CyclomaticComplexSecondGoodEx AS BEGIN SELECT * FROM ThirdTable; END; GO |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TYPE dbo.[Alter] FROM varchar(11) NOT NULL; CREATE TYPE [Order] FROM varchar(11) NOT NULL; EXEC sp_addtype N'Alter', N'char(10)',N'not null'; EXEC sp_addtype N'Table', N'char(10)',N'not null'; EXEC sys.sp_addtype [Alter], N'char(10)',N'not null'; EXEC sp_addtype N'AlterType', N'char(10)',N'not null'; EXEC sp_addtype N'TableType', N'char(10)',N'not null'; |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
CREATE PROCEDURE proc_InvalidCursorOpFirstFailedEx AS BEGIN OPEN MyFirstFailedCursor; END; GO CREATE PROCEDURE proc_InvalidCursorOpSecondFailedEx AS BEGIN OPEN MySecondFailedCursor; FETCH NEXT FROM MySecondFailedCursor; END; GO CREATE PROCEDURE proc_InvalidCursorOpThirdFailedEx AS BEGIN OPEN MyThirdFailedCursor; CLOSE MyThirdFailedCursor; END; GO CREATE PROCEDURE proc_InvalidCursorOpFourthFailedEx AS BEGIN DEALLOCATE MyFourthFailedCursor; END; GO CREATE PROCEDURE proc_InvalidCursorOpFirstGoodEx AS BEGIN DECLARE MyFirstGoodCursor CURSOR LOCAL FOR SELECT * FROM FirstTable; OPEN MyFirstGoodCursor; FETCH NEXT FROM MyFirstGoodCursor; CLOSE MyFirstGoodCursor; DEALLOCATE MyFirstGoodCursor; END; GO CREATE PROCEDURE proc_InvalidCursorOpSecondGoodEx AS BEGIN DECLARE MySecondGoodCursor CURSOR GLOBAL READ_ONLY FOR SELECT * FROM SecondTable; OPEN MySecondGoodCursor; FETCH NEXT FROM MySecondGoodCursor; CLOSE MySecondGoodCursor; DEALLOCATE MySecondGoodCursor; END; GO |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
CREATE PROC proc_MissNOCOUNTDMLFirstFailedEx AS SELECT * FROM FirstTable; GO CREATE PROC proc_MissNOCOUNTDMLSecondFailedEx AS BEGIN SELECT * FROM FirstTable; SET NOCOUNT ON; SELECT * FROM SecondTable; END; GO CREATE PROC proc_MissNOCOUNTDMLThirdFailedEx AS BEGIN SELECT * FROM FirstTable; SET NOCOUNT ON; SELECT * FROM SecondTable; SET NOCOUNT ON; SELECT * FROM ThirdTables; END; GO CREATE PROC proc_MissNOCOUNTDMLFirstGoodEx AS BEGIN SET NOCOUNT ON; SELECT * FROM FirstTable; SET NOCOUNT OFF; END; GO |
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:
|
1 2 3 4 5 6 7 |
INSERT INTO FirstTable (col1, col2) VALUES (1, 2); INSERT INTO SecondTable (col3, col4) VALUES (5, 7); INSERT INTO ThirdTable EXECUTE proc_InsertData; INSERT INTO FourthTable EXECUTE proc_InsertData; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
CREATE TABLE Logins (LoginName NVARCHAR(100) NOT NULL, Pass NVARCHAR(50) NOT NULL ); GO CREATE TRIGGER triggerWithPrintFirstFailedEx ON Logins FOR INSERT AS SELECT * FROM Logins; PRINT 'test1'; GO CREATE TRIGGER triggerWithPrintSecondFailedEx ON Logins FOR UPDATE AS SELECT * FROM Logins; PRINT 'test2'; GO CREATE TRIGGER triggerWithPrintThirdFailEx ON Logins FOR DELETE AS SELECT * FROM Logins; PRINT 'test3'; PRINT 'test4'; GO CREATE TRIGGER triggerWithPrintFirstGoodEx ON Logins FOR INSERT, UPDATE, DELETE AS EXEC PassLog; GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
CREATE PROCEDURE proc_TransTryCatchFirstFailedEx AS BEGIN BEGIN TRANSACTION; DELETE FROM FirstTable WHERE col1 > 0; COMMIT; END; GO CREATE PROCEDURE proc_TransTryCatchSecondFailedEx AS BEGIN BEGIN TRANSACTION; INSERT INTO SecondTable VALUES(1,2); INSERT INTO SecondTable VALUES(3,4); COMMIT; END; GO CREATE PROCEDURE proc_TransTryCatchThirdFailedEx AS BEGIN BEGIN TRANSACTION DemoTrans; INSERT INTO SecondTable VALUES(1,2); INSERT INTO SecondTable VALUES(3,4); COMMIT TRANSACTION DemonTrans; END; GO CREATE PROCEDURE proc_TransTryCatchFirstGoodEx AS BEGIN BEGIN TRY BEGIN TRANSACTION GoodTrans; INSERT INTO SecondTable VALUES(1,2); INSERT INTO SecondTable VALUES(3,4); COMMIT TRANSACTION GoodTrans; END TRY BEGIN CATCH PRINT 'Error'; END CATCH; END; GO CREATE PROCEDURE proc_TransTryCatchSecondGoodEx AS BEGIN BEGIN TRY BEGIN TRANSACTION SecondGoodTrans; DELETE FROM FirstTable WHERE col1 > 0; COMMIT; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg; END CATCH; END; GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE PROCEDURE proc_UndocumentSPFirstFailedEx AS BEGIN EXEC sp_blockcnt; END; GO CREATE PROCEDURE proc_UndocumentSPSecondFailedEx AS BEGIN EXEC sp_checknames; END; GO CREATE PROCEDURE proc_UndocumentSPThirdFailedEx AS BEGIN EXEC sp_column_rowset; END; GO CREATE PROCEDURE proc_UndocumentSPFirstGoodEx AS BEGIN DECLARE @handle INT; EXEC sp_xml_preparedocument @handle OUTPUT; END; GO |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
CREATE PROCEDURE proc_NonRecommendFirstFailedEx WITH RECOMPILE AS BEGIN SET ANSI_NULLS OFF; EXEC sp_blockcnt; END; GO CREATE PROCEDURE proc_NonRecommendSecondFailedEx WITH RECOMPILE AS BEGIN SET ARITHABORT OFF; SET ANSI_WARNINGS OFF; SET CONCAT_NULL_YIELDS_NULL OFF; EXEC sp_blockcnt; END; GO CREATE PROCEDURE proc_NonRecommendThirdFailedEx WITH RECOMPILE AS BEGIN SET CONCAT_NULL_YIELDS_NULL OFF; EXEC sp_blockcnt; END; GO CREATE PROCEDURE proc_NonRecommendFirstGoodEx AS BEGIN DECLARE @handle INT; EXEC sp_xml_preparedocument @handle OUTPUT; END; GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
CREATE PROCEDURE proc_THROWRollbackFirstFailedEx AS BEGIN BEGIN TRANSACTION; BEGIN TRY INSERT INTO testUniq(ID) VALUES(5); INSERT INTO testUniq(ID) VALUES(5); END TRY BEGIN CATCH ROLLBACK THROW; END CATCH; COMMIT; END; GO CREATE PROCEDURE procTHROWRollbackFirstGoodEx AS BEGIN BEGIN TRANSACTION; BEGIN TRY INSERT INTO testUniq(ID) VALUES(5); INSERT INTO testUniq(ID) VALUES(5); END TRY BEGIN CATCH ROLLBACK; THROW; END CATCH; COMMIT; END; GO |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
CREATE PROCEDURE proc_GrantNoGOFirstFailedEx AS BEGIN DECLARE MyFirstFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MyFirstFailedCursor; FETCH NEXT FROM MyFirstFailedCursor; END; GRANT EXECUTE ON proc_GrantNoGOFirstFailedEx TO TestRole WITH GRANT OPTION; GO CREATE PROCEDURE proc_GrantNoGOSecondFailedEx AS BEGIN DECLARE MySecondFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MySecondFailedCursor; FETCH NEXT FROM MySecondFailedCursor; END; GRANT EXECUTE ON proc_GrantNoGOSecondFailedEx TO SecondTestRole WITH GRANT OPTION; GO CREATE PROCEDURE proc_GrantNoGOThirdFailedEx AS BEGIN DECLARE MyThirdFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MyThirdFailedCursor; FETCH NEXT FROM MyThirdFailedCursor; CLOSE MyThirdFailedCursor; END; GRANT EXECUTE ON proc_GrantNoGOThirdFailedEx TO ThirdTestRole WITH GRANT OPTION; GO CREATE PROCEDURE proc_GrantNoGOFirstGoodEx AS BEGIN DECLARE MyFirstGoodCursor CURSOR LOCAL FOR SELECT * FROM FirstTable; OPEN MyFirstGoodCursor; FETCH NEXT FROM MyFirstGoodCursor; CLOSE MyFirstGoodCursor; DEALLOCATE MyFirstGoodCursor; END; GO GRANT EXECUTE ON proc_GrantNoGOFirstGoodExTO TO FourthTestRole WITH GRANT OPTION; GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
CREATE PROCEDURE proc_DuplicateCursorsFirstFailedEx AS BEGIN DECLARE MyFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MyFailedCursor; FETCH NEXT FROM MyFailedCursor; END; GO CREATE PROCEDURE proc_DuplicateCursorsSecondFailedEx AS BEGIN DECLARE MyFailedCursor CURSOR FOR SELECT * FROM SecondTable; OPEN MyFailedCursor; FETCH NEXT FROM MyFailedCursor; END; GO CREATE PROCEDURE proc_DuplicateCursorsThirdFailedEx AS BEGIN DECLARE MyFailedCursor CURSOR FOR SELECT * FROM ThirdTable; OPEN MyFailedCursor; FETCH NEXT FROM MyFailedCursor; CLOSE MyFailedCursor; END; GO CREATE PROCEDURE proc_DuplicateCursorsFirstGoodEx AS BEGIN DECLARE MyFirstGoodCursor CURSOR LOCAL FOR SELECT * FROM FirstTable; OPEN MyFirstGoodCursor; FETCH NEXT FROM MyFirstGoodCursor; CLOSE MyFirstGoodCursor; DEALLOCATE MyFirstGoodCursor; END; GO CREATE PROCEDURE proc_DuplicateCursorsSecondGoodEx AS BEGIN DECLARE MySecondGoodCursor CURSOR GLOBAL READ_ONLY FOR SELECT * FROM SecondTable; OPEN MySecondGoodCursor; FETCH NEXT FROM MySecondGoodCursor; CLOSE MySecondGoodCursor; DEALLOCATE MySecondGoodCursor; END; GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
CREATE PROCEDURE proc_CursorNoScopeFirstFailedEx AS BEGIN DECLARE MyFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MyFailedCursor; FETCH NEXT FROM MyFailedCursor; END; GO CREATE PROCEDURE proc_CursorNoScopeSecondailedEx AS BEGIN DECLARE MySecondFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MySecondFailedCursor; FETCH NEXT FROM MySecondFailedCursor; END; GO CREATE PROCEDURE proc_CursorNoScopeThirdFailedEx AS BEGIN DECLARE MyThirdFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MyThirdFailedCursor; FETCH NEXT FROM MyThirdFailedCursor; CLOSE MyThirdFailedCursor; END; GO CREATE PROCEDURE proc_CursorNoScopeFirstGoodEx AS BEGIN DECLARE MyFirstGoodCursor CURSOR LOCAL FOR SELECT * FROM FirstTable; OPEN MyFirstGoodCursor; FETCH NEXT FROM MyFirstGoodCursor; CLOSE MyFirstGoodCursor; DEALLOCATE MyFirstGoodCursor; END; GO CREATE PROCEDURE proc_CursorNoScopeSecondGoodEx AS BEGIN DECLARE MySecondGoodCursor CURSOR GLOBAL READ_ONLY FOR SELECT * FROM SecondTable; OPEN MySecondGoodCursor; FETCH NEXT FROM MySecondGoodCursor; CLOSE MySecondGoodCursor; DEALLOCATE MySecondGoodCursor; END; GO |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
USE FirstDemoDB; CREATE PROCEDURE proc_HardcodedDBNameFirstFailedEx AS BEGIN EXEC FirstDemoDB.Proc_FirstSP @val1 = N'ABC'; END; GO USE SecondDemoDB; GO CREATE PROCEDURE proc_HardcodedDBNameSecondFailedEx AS BEGIN EXEC SecondDemoDB.proc_SecondSPs @val2 = N'ABC'; END; GO USE ThirdDemoDB; GO CREATE PROCEDURE proc_HardcodedDBNameThirdFailedEx AS BEGIN EXEC ThirdDemoDB.proc_ThirdSP @val3 = N'ABC'; END; GO USE EnforceDemoDB; CREATE PROCEDURE proc_HardcodedDBNameFirstGoodEx AS BEGIN EXEC proc_FourthSP @val4 = N'ABC'; END; GO GO USE FourthDemoDB; CREATE PROCEDURE proc_HardcodedDBNameSecondGoodEx AS BEGIN EXEC proc_FifthSP @val5 = N'ABC'; END; GO |
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
Example script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
CREATE PROCEDURE proc_BeginEndBlockFirstFailedEx AS BEGIN DECLARE MyFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MyFailedCursor; FETCH NEXT FROM MyFailedCursor; END; PRINT 'test1'; PRINT 'bad line after proc'; GO CREATE PROCEDURE proc_BeginEndBlockSecondFailedEx AS BEGIN DECLARE MySecondFailedCursor CURSOR FOR SELECT * FROM FirstTable; OPEN MySecondFailedCursor; FETCH NEXT FROM MySecondFailedCursor; END; PRINT 'test2'; GO CREATE PROCEDURE proc_BeginEndBlockFirstGoodEx AS BEGIN DECLARE MyFirstGoodCursor CURSOR LOCAL FOR SELECT * FROM FirstTable; OPEN MyFirstGoodCursor; FETCH NEXT FROM MyFirstGoodCursor; CLOSE MyFirstGoodCursor; DEALLOCATE MyFirstGoodCursor; END; GO GO CREATE PROCEDURE proc_BeginEndBlockSecondGoodEx AS BEGIN DECLARE MySecondGoodCursor CURSOR GLOBAL READ_ONLY FOR SELECT * FROM SecondTable; OPEN MySecondGoodCursor; FETCH NEXT FROM MySecondGoodCursor; CLOSE MySecondGoodCursor; DEALLOCATE MySecondGoodCursor; END; GO |
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:
|
1 2 3 4 5 |
ALTER TABLE FirstTable DISABLE TRIGGER MySecondTrigger; ALTER TABLE SecondTable DISABLE TRIGGER ALL; ALTER TABLE ThirdTable DISABLE TRIGGER ALL; ALTER TABLE FourthTable ALTER COLUMN Col2 VARCHAR(50); ALTER TABLE FifthTable ALTER COLUMN Col2 VARCHAR(50); |
Message:
Trigger(s) is disabled, consider enabling it
- Best author award in 2021 - January 3, 2022
- Best author award in 2020 - January 5, 2021
- Best author award in 2019 - January 3, 2020