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.
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 .
The NOT operator displays a record if the condition(s) is NOT TRUE.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With