SQL Server – Prevent Compilation of Stored Procedure to Linked Server

There are a couple of options available. One of them is to use dynamic SQL for your insert calls. That way, validation doesn't actually occur until the execution occurs. For your example, it could look like:

declare @sql nvarchar(max);

IF (@flagA = 1)
BEGIN
set @sql = N'
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT A, B, C
    FROM (
        SELECT *
        FROM OPENROWSET(
            ''Microsoft.ACE.OLEDB.12.0'', 
            ''Text; HDR=Yes; Database=\\server\tmpFiles'', 
            ''SELECT * FROM FileA.txt'')
    ) as T
';
END

IF (@flagB = 1)
BEGIN
set @sql = N'
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT D, E, F
    FROM (
        SELECT *
        FROM OPENROWSET(''Microsoft.Ace.OLEDB.12.0'', 
        ''Excel 8.0;Database=\\server\tmpFiles\FileB.xls'', 
        ''SELECT * FROM [Sheet$]'')
    ) as T
';
END

IF (@flagC = 1)
BEGIN
set @sql = N'
    INSERT INTO #tmpData(Column1, Column2, Column3)
    SELECT G, H, I
    FROM (
        SELECT *
        FROM OPENROWSET(''Microsoft.Ace.OLEDB.12.0'', 
        ''Excel 8.0;Database=\\server\tmpFiles\FileC.xls'', 
        ''SELECT * FROM [Sheet1$]'')
    ) as T
';
END

execute sp_executesql @sql;

In this case, validation wouldn't occur until runtime. The downside is that troubleshooting gets harder because you're now trying to debug dynamic SQL.

You might also be able to merge the several separate statements into one.

A second alternative is to split the call out into separate procedures. I would put this at a distant second-place, mostly because it's easy to abuse this and start pretending that T-SQL stored procedures are equivalent to C-style methods, forgetting that there can be significant performance differences. Anyhow, move the INSERT statements into three separate procedures and pass in any necessary parameters, like so:

IF (@flagA = 1)
BEGIN
    exec InsertFlagA @parms1;
END

IF (@flagB = 1)
BEGIN
    exec InsertFlagB @parms2;
END

IF (@flagC = 1)
BEGIN
    exec InsertFlagB @parms3;
END

You couldn't do this with a temp table, so that might throw this option out unless you had a permanent "temporary" table available (or used a global temp table).

If you did want to use a temp table, you could change the exec statement to insert into #tmpData exec InsertFlagA @parms1.

As others have said, you have problems which are goign to require serious re-evaluation of how you do business. You can keep patching things with virtual duct-tape, or you can come up with a plan to fix the problem.

However, a duct-tape solution for this particular problem would be to check for the existance of those columns first (using information_schema.columns), and then create the appropriate version of the stored procedure (or not at all). This will continue to create havoc, but it will at least buy you some time.