Sql-server – How to stop SQL from validating an OPENROWSET query when its not going to be executed

sql serversql-server-2005

I have a problem where a stored procedure is throwing an error because it's trying to run/validate an OPENROWSET query inside it that it doesn't actually have to execute.

The stored procedure that looks something like this:

IF (@flagA = 1)
BEGIN
    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
    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
    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

Note that each OPENROWSET references a different file, in a different format, with different column names.

My problem is that if one of the files doesn't exist, or exists in the wrong format (such as someone uploading a FileB as FileC by mistake), I get an error when the procedure tries to run, even though that OPENROWSET query is never meant to get executed.

For example, if FileB doesn't exist and you try to run the procedure with @flagB = 0, it will throw an error saying it can't find the linked server for FileB

Or if a user uploads a FileB as a FileC by mistake, then corrects themselves by removing FileC from the form, it will throw an error saying Cannot process the object "SELECT * FROM [Sheet1$]" next time the procedure runs because FileC won't have a sheet named Sheet1, even though @flagC = 0

How can I prevent SQL from validating the OPENROWSET queries if they're not the ones getting executed?

Best Answer

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.

Related Question