Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-Sql appears to be evaluating "If" statement even when the condition is not true

I have a T-Sql script where part of this script checks to see if a certain column exists in the a table. If so, I want it to execute a routine... if not, I want it to bypass this routine. My code looks like this:

IF COL_LENGTH('Database_Name.dbo.Table_Name', 'Column_Name1') IS NOT NULL
    BEGIN
        UPDATE Table_Name
            SET Column_Name2 = (SELECT Column_Name3 FROM Table_Name2
                WHERE Column_Name4 = 'Some Value')
            WHERE Column_Name5 IS NULL;

        UPDATE Table_Name
            SET Column_Name6 = Column_Name1
            WHERE Column_Name6 IS NULL;
    END

My problem is that even when COL_LENGTH of Column_Name1 is null (meaning it does not exist) I am still getting an error telling me "Invalid column name 'Column_Name1'" from the 2nd UPDATE statement in the IF statement. For some reason this IF condition is still being evaluated even when the condition is FALSE and I don't know why.

like image 785
Chris Zilligen Avatar asked Nov 06 '13 21:11

Chris Zilligen


People also ask

Does SQL CASE evaluate all conditions?

A CASE expression evaluates to the first true condition. If there is no true condition, it evaluates to the ELSE part. If there is no true condition and no ELSE part, it evaluates to NULL .

Which operator displays a record if the condition is not true?

The NOT operator displays a record if the condition(s) is NOT TRUE.

Can we write SQL query in if condition?

We can use SQL IF statement without ELSE as well. In the following, the expression evaluates to TRUE; therefore, it prints the message. If the expression evaluates to FALSE, it does not return any output. We should use ELSE statement so that if an evaluation is not TRUE, we can set default output.


1 Answers

SQL Server parses the statement and validates it, ignoring any if conditionals. This is why the following also fails:

IF 1 = 1
BEGIN
  CREATE TABLE #foo(id INT);
END
ELSE
BEGIN
  CREATE TABLE #foo(id INT);
END

Whether you hit Execute or just Parse, this results in:

Msg 2714, Level 16, State 1
There is already an object named '#foo' in the database.

SQL Server doesn't know or care which branch of a conditional will be entered; it validates all of the statements in a batch anyway. You can do things like (due to deferred name resolution):

IF <something>
BEGIN
  SELECT foo FROM dbo.Table_That_Does_Not_Exist;
END

But you can't do:

IF <something>
BEGIN
  SELECT column_that_does_not_exist FROM dbo.Table_That_Does;
END

The workaround, typically, is to use dynamic SQL:

IF <something>
BEGIN
  DECLARE @sql NVARCHAR(MAX);
  SET @sql = N'SELECT column_that_does_not_exist FROM dbo.Table_That_Does;';
  EXEC sp_executesql @sql;
END
like image 128
Aaron Bertrand Avatar answered Oct 16 '22 12:10

Aaron Bertrand