SQL code smells rules

APX1109 – QUOTED_IDENTIFIERS or ANSI_NULLS in a programmability object

Description:

This rule evaluates the T-SQL script for using QUOTED_IDENTIFIERS or ANSI_NULLS options inside programmability objects.

Using QUOTED_IDENTIFIERS or ANSI_NULLS options inside programmability objects will be ignored.

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:

CREATE PROCEDURE proc_QIorANSIFirstFailedEx

BEGIN

SET QUOTED_IDENTIFIER OFF;

SET ANSI_NULLS OFF;

SELECT *

FROM FirstTable;

END;

CREATE PROCEDURE proc_QIorANSISecondFailedEx

BEGIN

SET ANSI_NULLS OFF;

SELECT *

FROM SecondTable;

END;

CREATE PROCEDURE proc_QIorANSIThirdFailedEx

BEGIN

SET QUOTED_IDENTIFIER OFF;

SELECT *

FROM ThirdTable;

END;

CREATE PROCEDURE proc_QIorANSIFirstGoodEx

BEGIN

SELECT *

FROM FourthTable;

END;

CREATE PROCEDURE QIorANSISecondGoodEx

BEGIN

SELECT *

FROM FifthTable;

END;

Message:

Consider removing QUOTED_IDENTIFIER or ANSI_NULLS type identifier as they are ignored in this context

APX1118 – Unassigned variable

Description:

This rule evaluates the T-SQL script for having variables that have been declared but never assigned or used, where it should be removed to streamline code.

Consider assigning values for the variables before its first use.

For more information visit

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

Example script:

DECLARE @FirstGoodVAR int;

DECLARE @SecondGoodVAR int;

DECLARE @ThirdGoodVAR int;

DECLARE @FourthGoodVAR int;

DECLARE @FifthGoodVAR int;

DECLARE @FirstFailedVAR int;

DECLARE @SecondFailedVAR int;

DECLARE @ThirdFailedVAR int;

DECLARE @ResultVAR int;

SET @ResultVAR = @FirstGoodVAR + @SecondGoodVAR + @ThirdGoodVAR + @FourthGoodVAR + @FifthGoodVAR;

Message:

Variable @’*’FailedVAR is declared but not used

APX1143 – Potential “unfinished” development

Description:

The presence of keywords like, TEST, TODO, HACK and UNDONE in comments can indicate unfinished work has migrated to Test or Production.

Edit the rule to add/update/remove key words

Example script:

CREATE PROCEDURE proc_UnfinishFirstFailedEx

BEGIN

--TODO

SELECT *

FROM FirstTable;

END;

CREATE PROCEDURE UnfinishSecondFailedEx

BEGIN

--HACK

SELECT *

FROM SecondTable;

END;

CREATE PROCEDURE UnfinishThirdFailedEx

BEGIN

--UNDONE

SELECT *

FROM ThirdTable;

END;

CREATE PROCEDURE UnfinishFirstGoodEx

BEGIN

SELECT *

FROM FirstTable;

END;

CREATE PROCEDURE UnfinishSecondGoodEx

BEGIN

SELECT *

FROM SecondTable;

END;

Message:

Potential unfinished work detected – ‘*’

APX1146 – Unused variable

Description:

This rule evaluates the T-SQL script for never using the variables, even if they are assigned, where these variables should be removed to streamline code.

Make sure to declare only the variables that you need in your code and remove all unused variables.

For more information visit

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

Example script:

DECLARE @FirstFailedVAR INT;

DECLARE @SecondFailedVAR INT;

DECLARE @ThirdFailedVAR INT;

DECLARE @FourthFailedVAR INT;

DECLARE @FifthFailedVAR INT;

DECLARE @FirstGoodVAR INT;

DECLARE @SecondGoodVAR INT;

SET @FirstFailedVAR = 3;

SET @SecondFailedVAR = 4;

SET @ThirdFailedVAR = 3;

SET @FourthFailedVAR = 4;

SET @FifthFailedVAR = 5;

SET @FirstGoodVAR = 12;

SET @SecondGoodVAR = @FirstGoodVAR * 6;

SELECT *

FROM FirstTable

WHERE val = @SecondGoodVAR;

PRINT @FifthFailedVAR;

SET @FifthFailedVAR = 25;

Message:

Unused variable, @bad1, detected

APX1190 – Output parameter never assigned

Description:

This rule evaluates the T-SQL script for not assigning values to the output parameters.

Make sure to assign value to the SP OUTPUT parameters.

For more information visit

https://docs.microsoft.com/en-us/sql/connect/jdbc/using-a-stored-procedure-with-output-parameters?view=sql-server-2017

Example script:

CREATE PROCEDURE proc_OUTParamNoAssignFirstFailedEx @param1 NVARCHAR(30),

@param2 INT OUTPUT

BEGIN

SELECT *

FROM FirstTable

WHERE col1 = @param1;

END;

CREATE PROCEDURE OUTParamNoAssignSecondFailedEx @param1 NVARCHAR(30),

@param2 INT OUTPUT,

@param3 INT OUTPUT

BEGIN

SELECT *

FROM FirstTable

WHERE col1 = @param1;

END;

CREATE PROCEDURE OUTParamNoAssignThirdFailedEx @param1 NVARCHAR(30),

@param2 INT OUTPUT,

@param3 INT OUTPUT

BEGIN

SELECT *

FROM FirstTable

WHERE col1 = @param1;

SET @param3 = @@ROWCOUNT;

END;

CREATE PROCEDURE OUTParamNoAssignFirstGoodEx @param1 NVARCHAR(30),

@param2 INT OUTPUT,

@param3 INT OUTPUT

BEGIN

SELECT *

FROM FirstTable

WHERE col1 = @param1;

SET @param2 = 115;

SET @param3 = @@ROWCOUNT;

END;

CREATE PROCEDURE OUTParamNoAssignSecondGoodEx @param1 NVARCHAR(30),

@param2 INT OUTPUT,

@param3 INT OUTPUT

BEGIN

SELECT *

FROM FirstTable

WHERE col1 = @param1;

SET @param2 = 125;

SET @param3 = @@ROWCOUNT;

END;

Message:

Output parameter, @param’*’, isn’t used

APX1203 – Duplicate identifier

Description:

This rule evaluates the T-SQL script for using the same name for different objects.

Try to use unique names for the different database objects.

Example script:

CREATE TABLE foo(foo1 INT NOT NULL);

CREATE INDEX foo ON foo(foo1);

CREATE TABLE AA

(AA INT,

BB INT

);

CREATE INDEX AA ON AA(AA, BB);

Message:

Object [foo] already exists

APX1224 – Comment header

Description:

This rule evaluates the T-SQL script for NOT using comment header blocks.

Comment header improve code maintainability by making the programmability object self-documenting.

For more information visit

https://sqlserverfunctions.wordpress.com/2014/10/13/t-sql-code-header-comments/

Example script:

CREATE PROCEDURE proc_CommentHeaderFirstGoodEx

BEGIN

--Comment for FirstGoodProc

DECLARE MyFirstGoodCursor CURSOR LOCAL

FOR SELECT *

FROM FirstTable;

OPEN MyFirstGoodCursor;

FETCH NEXT FROM MyFirstGoodCursor;

CLOSE MyFirstGoodCursor;

DEALLOCATE MyFirstGoodCursor;

END;

CREATE PROCEDURE proc_CommentHeaderSecondGoodEx

BEGIN

--Comment for SecondGoodProc

DECLARE MySecondGoodCursor CURSOR GLOBAL READ_ONLY

FOR SELECT *

FROM SecondTable;

OPEN MySecondGoodCursor;

FETCH NEXT FROM MySecondGoodCursor;

CLOSE MySecondGoodCursor;

DEALLOCATE MySecondGoodCursor;

END;

CREATE PROCEDURE proc_CommentHeaderFirstFailedEx

BEGIN

DECLARE MyFirstFailedCursor CURSOR

FOR SELECT *

FROM FirstTable;

OPEN MyFirstFailedCursor;

FETCH NEXT FROM MyFirstFailedCursor;

END;

CREATE PROCEDURE proc_CommentHeaderSecondFailedEx

BEGIN

DECLARE MySecondFailedCursor CURSOR

FOR SELECT *

FROM FirstTable;

OPEN MySecondFailedCursor;

FETCH NEXT FROM MySecondFailedCursor;

END;

CREATE PROCEDURE proc_CommentHeaderThirdFailedEx

BEGIN

DECLARE MyThirdFailedCursor CURSOR

FOR SELECT *

FROM FirstTable;

OPEN MyThirdFailedCursor;

FETCH NEXT FROM MyThirdFailedCursor;

CLOSE MyThirdFailedCursor;

END;

Message:

Comment header is missing for this CreateProcedure

Author
Recent Posts

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