is it okay to pre-check the data in T-SQL before execute the statement

Question

while I was working in the stored procedure to avoid the sting query in the server side, I came up with a question "why don't I validate the incoming data before executing the statement?" just before I go further with my work I decided to ask this a question in Stack Overflow because there are highly professional members.

This is an example of the work

Ttable

CREATE TABLE [dbo].[Ttable] (
    [TtableId]         INT            IDENTITY (1, 1) NOT NULL,
    [Ttablecol]        NVARCHAR (MAX) NOT NULL,
    [IsTtableIsActive] BIT            NOT NULL,
    PRIMARY KEY CLUSTERED ([TtableId] ASC)
);

example 1

CREATE PROCEDURE [dbo].[spAddToTtable]
    @param NVARCHAR(MAX) = ''
AS
BEGIN
    IF(NOT EXISTS 
      (SELECT NULL 
       FROM   Ttable
       WHERE  Ttablecol = @param))
    BEGIN
    INSERT 
    INTO    Ttable(Ttablecol,IsTtableIsActive)
    VALUES  (@param,1)
    SELECT SCOPE_IDENTITY() AS INT 
    END
    ELSE
    BEGIN
        IF(EXISTS 
          (SELECT NULL 
           FROM   Ttable
           WHERE  Ttablecol = @param 
             AND  IsTtableIsActive = 0 ))
        BEGIN
            SELECT -2 AS INT 
        END
        ELSE
        BEGIN
            SELECT -1 AS INT
        END
    END
END

example 2

CREATE PROCEDURE [dbo].[spInActiveTtable]
@param INT = 0
AS
BEGIN
    IF(EXISTS 
      (SELECT NULL 
       FROM   Ttable
       WHERE  IsTtableIsActive = 1))
    BEGIN
    UPDATE Ttable
    SET IsTtableIsActive = 0
    WHERE TtableId = @param
    SELECT SCOPE_IDENTITY() AS INT 
    END
    ELSE
    BEGIN
        IF(EXISTS 
          (SELECT NULL 
           FROM   Ttable
           WHERE  Ttablecol = @param 
             AND  IsTtableIsActive = 0 ))
        BEGIN
            SELECT -2 AS INT 
        END
        ELSE
        BEGIN
            SELECT -1 AS INT
        END
    END
END

example 3

CREATE PROCEDURE [dbo].[spReActiveTtable]
@param INT = 0
AS
BEGIN
    IF(EXISTS 
      (SELECT NULL 
       FROM   Ttable
       WHERE  IsTtableIsActive = 0))
    BEGIN
    UPDATE Ttable
    SET IsTtableIsActive = 1
    WHERE TtableId = @param
    SELECT SCOPE_IDENTITY() AS INT 
    END
    ELSE
    BEGIN
        IF(EXISTS 
          (SELECT NULL 
           FROM   Ttable
           WHERE  Ttablecol = @param 
             AND  IsTtableIsActive = 1 ))
        BEGIN
            SELECT -3 AS INT 
        END
        ELSE
        BEGIN
            SELECT -1 AS INT
        END
    END
END

example 4

CREATE PROCEDURE [dbo].[spModifyTtable]
@param INT = 0,
@param2 nvarchar(max) = ''
AS
BEGIN
    IF((SELECT  Ttablecol 
          FROM  Ttable
          WHERE TtableId = @param) = @param2)
          BEGIN 
          SELECT -5 AS INT 
          END 
    ELSE 
    BEGIN
    UPDATE  Ttable
    SET     Ttablecol = @param2
    SELECT -6 AS INT 
    END 
END

Is what I'm doing correct?

Thanks a lot for your help and I wish that my question is useful

Edmond Quinton · Accepted Answer

The answer to your question is the hated “It depends”.

The real question is what is you are trying to achieve? If you are simply trying to enforce data integrity I would stick with uniqueness constraints, foreign keys etc. where possible, otherwise you are simply doing work that SQL Server can do for you (and more efficiently as well).

If your calling application needs to perform different business logic depending on the existence a record or you cannot enforce data integrity constraint via standard means, then this approach is fine in my opinion. However, but be sure that there is actual value in performing these additional checks since you are now adding additional complexity and performance overheads to your queries. As the database grows over time you may find that these extra check are slowing you down substantially.

Your first example is a good use of the existence check since you cannot add a uniqueness constraint on a NVARCHAR(MAX) column. Something to keep in mind though is now you are preforming a search on an a potentially very large column that is not indexed. Depending on how large your table grows this could become a very expensive query over time. If your table will stay relatively small, then this will be less of a concern.

I am less enthusiastic about your other example. At a minimum you are always performing 2 select which from what I can see does not really help enforce any constraints or provide any addition information you could have derived via other means.

Let take you second query as an example, your existence check will hit your IsTtableIsActive column which is not indexed which means you can’t effectively leverage the clustered index you have on your ID column. Also you will never hit your 3rd if statement since IsTtableIsActive does not allow null values. Your second example can we written as follow and will give the same result:

    CREATE PROCEDURE [dbo].[spInActiveTtable]
    @param INT = 0
    AS
    BEGIN

        DECLARE @affectedRows INT = 0;
        DECLARE @recordID INT = -1;

        UPDATE Ttable
        SET IsTtableIsActive = 0
        WHERE TtableId = @param AND IsTtableIsActive = 1

        SET @recordID = SCOPE_IDENTITY();
        SET @affectedRows = @@ROWCOUNT;

        IF @affectedRows > 0
            SET @recordID = -1;

        SELECT @recordID;

    END

The above query simply checks IsTtableIsActive = 1 in the were clause of the update statement itself. Your clustered index is now being utilized, SQL Server does not have to perform 2 scans of your table. If the @@ROWCOUNT returns 0 then we know the condition for the record, we tried update was not met.

is it okay to pre-check the data in T-SQL before execute the statement

Answers (2)

Related Questions