How to avoid syntax check before query is run?

Question

Here is my code:

CREATE TABLE [dbo].[TestTable](
ColumnA VARCHAR(20),
ColumnB VARCHAR(20))

INSERT INTO TestTable
SELECT 'TestA', 'TestB'

IF COL_LENGTH('TestTable', 'ColumnC') IS NOT NULL
BEGIN
    PRINT 'ColumnC Present'
    SELECT ColumnC FROM TestTable
END

As you can see it should only query ColumnC if it is actually present. However the mgmt studio blocks the execution of the whole query. Basically what I want is to query for the value of ColumnC if this column exists. I am writing a query for a software on multiple versions, some versions have this column older versions do not.

Kind of a duplicate question: http://stackoverflow.com/questions/133031/how-to-check-if-column-exists-in-sql-server-table — RadioActiveEd, Jun 24 '14 at 18:48
Not really, the second most popular answer on that question uses the `COL_LENGTH` method he is already using to check for existence. — Abe Miessler, Jun 24 '14 at 18:55

regeter · Answer 1 · 2014-06-24T19:06:04.930

This isn't about how to check for presence of a column. Rather this is about the pre-flight SQL check for names of tables and columns.

I could not figure out how to make the IF ELSE block work so instead I chose to craft a dynamic SQL statement based on the COL_LENGTH.

Here is the actual solution I've used. (my real query uses CTE, so I had to ensure it works fine with CTE as well)

DECLARE @IsBroken VARCHAR(20)
SET @IsBroken = 'ColumnB'

IF COL_LENGTH('TestTable', 'ColumnC') IS NOT NULL
BEGIN
    PRINT 'ColumnC Present'
    SET @IsBroken = 'ColumnC'
END

DECLARE @SQLStatement VARCHAR(MAX)
SET @SQLStatement = '
;WITH UpdateTypes AS
(
    SELECT ' + @IsBroken + ' FROM TestTable
)
SELECT * FROM UpdateTypes'
PRINT @SQLStatement
EXEC (@SQLStatement)

How to avoid syntax check before query is run?

1 Answers1