Ignore SQL Missing Columns During Execution Time

Question

I am working on a tool (using c# winforms) which generates SQL scripts of data which are independent of identities but yet not violating constraints. While walking through all possibilities I got into below trouble. I need to insert data considering the fact a column (which allows null) may exists or not. For example, a column as VAL2 exists in dev box, but may or may not exists in prod box.

CREATE TABLE TEMP ( COL1 VARCHAR(50) , VAL1  VARCHAR(50)) -- OPTIONAL/MAY EXISTS VAL2 VARCHAR(50)

IF EXISTS (SELECT TOP(1) COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TEMP' AND COLUMN_NAME = 'VAL2')
BEGIN
INSERT INTO TEMP (COL1,VAL1,VAL2) VALUES ('1','YES','NO')
INSERT INTO TEMP (COL1,VAL1,VAL2) VALUES ('2','NO','NO')
END
ELSE
BEGIN
INSERT INTO TEMP (COL1,VAL1) VALUES ('1','YES')
INSERT INTO TEMP (COL1,VAL1) VALUES ('2','NO')
END
GO

But this method fails miserably if that column is missing even though its should be flowing through else by syntax.

Note: I had for now made a work around but adding that column and dropping it at end if only created through current script. But the problem is the execution is through limited access grant which rules possibility of CREATE/DROP statement.This may be wrong, but that's the way it is for me.

If there is any alternate way preferably which doesn't mandates adding this column , please do guide me.

Answer 1

You'll have to use dynamic sql to accomplish that:

CREATE TABLE TEMP
    (
      COL1 VARCHAR(50) ,
      VAL1 VARCHAR(50)
    )
 -- OPTIONAL/MAY EXISTS VAL2 VARCHAR(50)

IF EXISTS ( SELECT TOP ( 1 )
                    COLUMN_NAME
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE   TABLE_NAME = 'TEMP'
                    AND COLUMN_NAME = 'VAL2' )
    BEGIN
        DECLARE @sql NVARCHAR(MAX)
        SELECT  @sql = 'INSERT INTO TEMP (COL1,VAL1,VAL2) VALUES (''1'',''YES'',''NO'')
INSERT INTO TEMP (COL1,VAL1,VAL2) VALUES (''2'',''NO'',''NO'')'
        EXEC sp_executesql @sql
    END
ELSE
    BEGIN
        INSERT  INTO TEMP
                ( COL1, VAL1 )
        VALUES  ( '1', 'YES' )
        INSERT  INTO TEMP
                ( COL1, VAL1 )
        VALUES  ( '2', 'NO' )
    END