Short-circuiting tables-CodePudding

I'm upgrading several identical copies of a database which may already be upgraded partially, and for some reason bool values were stored in an nvarchar(5).

So in the below, (which exists inside an INSERT > SELECT block), I need to check if the column ShowCol exists, fill it with 0 if it does not, or fill it with the result of evaluating the string bool if it does:

CASE
    WHEN COL_LENGTH('dbo.TableName', 'ShowCol') IS NULL THEN 0
    ELSE IIF(LOWER(ShowCol) = 'false', 0, 1)
END

...but I'm getting an error "Invalid column name 'ShowCol'". I can't seem to short-circuit this, can you help?

Its worth noting that the column if it does exist contains a mix of "false", "False" and "FALSE", so that's the point of the LOWER(). (The True column also occasional trailing spaces to contend with, which is why I'm just dealing with False and everything else is true.)

I suspect that its because of this wrap in LOWER() which is causing the server to always evaluate the expression.

CodePudding user response：

You can’t short circuit the existence of a column (and it has nothing to do with LOWER(); if you remove it, nothing will change).

You’ll need dynamic SQL, e.g.:

DECLARE @sql nvarchar(max) = N'UPDATE trg SET
  trg.col1 = src.col1,
  trg.col2 = src.col2';

IF COL_LENGTH('dbo.TableName', 'ShowCol') > 0
BEGIN
  SET @sql  = N', trg.ShowCol = IIF(LOWER(src.ShowCol) = ''false'', 0, 1)';
END

SET @sql  = N' ...
  FROM dbo.TableName AS trg
  INNER JOIN dbo.Origin AS src
  ON ...';

EXEC sys.sp_executesql @sql; -- ,N'params', @params;

When you're selecting data, you can fool the parser a little bit by introducing constants to take the place of columns, taking advantage of SQL Server's desire to find a column reference even at a different scope than the syntax would suggest. I talk about this in Make SQL Server DMV Queries Backward Compatible. I don't know of any straightforward way to make that work with writes without dynamic SQL, as the parser does more strict checking there, so it's harder to fool.

Imagine you have these tables:

CREATE TABLE dbo.SourceTable(a int, b int, c int);
INSERT dbo.SourceTable(a,b,c) VALUES(1,2,3);

CREATE TABLE dbo.DestinationWithAllColumns(a int, b int, c int);
INSERT dbo.DestinationWithAllColumns(a,b,c) VALUES(1,2,3);

CREATE TABLE dbo.DestinationWithoutAllColumns(a int, b int);
INSERT dbo.DestinationWithoutAllColumns(a,b) VALUES(1,2);

You can write a SELECT against either of them that produces an int output column called c:

;WITH optional_columns AS
(
  SELECT c = CONVERT(int, NULL)
)
SELECT trg.a, trg.b, trg.c
FROM optional_columns
CROSS APPLY 
  (SELECT a,b,c FROM dbo.DestinationWithAllColumns) AS trg
INNER JOIN dbo.SourceTable AS src ON src.a = trg.a;

Output:

a	b	c
1	2	3

;WITH optional_columns AS
(
  SELECT c = CONVERT(int, NULL)
)
SELECT trg.a, trg.b, trg.c 
FROM optional_columns
CROSS APPLY 
  (SELECT a,b,c FROM dbo.DestinationWithoutAllColumns) AS trg
INNER JOIN dbo.SourceTable AS src ON src.a = trg.a;

Output:

a	b	c
1	2	null

So far, so good. But as soon as you try and update:

;WITH optional_columns AS
(
  SELECT c = CONVERT(int, NULL)
)
UPDATE trg SET trg.b = src.b, trg.c = src.c
FROM optional_columns
CROSS APPLY 
  (SELECT a,b,c FROM dbo.DestinationWithoutAllColumns) AS trg
INNER JOIN dbo.SourceTable AS src ON src.a = trg.a;

Msg 4421, Level 16, State 1
Derived table 'trg' is not updatable because a column of the derived table is derived or constant.

Example db<>fiddle