SQL Server 2005: "Protecting" stored procedures from FMTONLY mode used by MS Access

Question

Some of the stored procedures we have contain conditional logic, like this:

Create Procedure dbo.DoSomething(Some Parameters)
As
    ...
    If (Some Condition) Begin
        Set @SomeVariable = SomeValue                
    End
    ...
    Select ...

When such a stored procedure is used as a recordsource for an MS Access form, and user tries to use built-in sorting/filtering functionality of the form, MS Access tries to execute the stored procedure in FMTONLY mode (apparently, hunting for metadata of the rowset provided by the stored procedure).

As most people know (now including ourselves :-), when FMTONLY is set ON, SQL Server ignores conditional statements. In the example shown below, the Set @SomeVariable = SomeValue statement is executed regardless of whether Some Condition is true, which obviously creates some troubles for us.

-- EXAMPLE
-- -------
Create Procedure dbo.DoSomething(..., @vcSomeDate as VarChar(50), ...)
As
   ...
   Declare @dtSomeDate As Datetime
   If (IsDate(@vcSomeDateOrAgeInDays)) Begin
       -- The next statement fails miserably when FMTONLY=ON
       Set @dtSomeDate = @vcSomeDateOrAgeInDays
   End Else Begin
       ...
   End
   ...

In order to circumvent this problem, we "wrap" conditional logic (or whatever other code fragments affected by FMTONLY) like this:

Create Procedure dbo.DoSomething(Some Parameters)
As
    ...

    -- HACK: Protection from unexpected FMTONLY mode
    Declare @wasFmtonlyOn As Bit; If (0 = 1) Set @wasFmtonlyOn = 1; SET FMTONLY OFF
    ...
    If (Some Condition) Begin
        Set @SomeVariable = SomeValue                
    End
    ...
    -- /HACK: Protection from unexpected FMTONLY mode
    If (@wasFmtonlyOn = 1) SET FMTONLY ON

    ...
    Select ...

(This ugly one-line formatting of the "protection code" is intentional: we believe that hacks required to solve some weird problems do not deserve proper formatting; quite the contrary, we believe that they should fit into as few lines of code as possible. :-)

Anyway, this "protection" works fine, but it is somewhat too verbose and not as much encapsulated as we would want it to be. For example, we'd definitely prefer to hide the actual logic of the hack - for example behind a scalar UDF like this:

Create Procedure dbo.DoSomething(Some Parameters)
As
    ...

    declare @wasFmtonlyOn as bit; set @wasFmtonlyOn = dbo.SetFmtonly(0)
    ...
    If (Some Condition) Begin
        Set @SomeVariable = SomeValue                
    End
    ...
    dbo.SetFmtonly(@wasFmtonlyOn)

    ...
    Select ...

Unfortunately, this does not seem to work - neither with scalar UDFs, not with another stored procedure. It looks like FMTONLY prevents return of any data from anywhere. So, here comes The Main Question:

If you also had to deal with this problem (SQL Server's ignoring conditionals in FMTONLY mode), were you able to come up with a better "protection idiom" than the one described above?

BTW, I still don't understand one thing: Is this problem a bug or a feature in SQL Server 2005? And if it is a feature, then what could possibly be a good reason for it?

Thank you!

Answer 1

What about this?

If (Some Condition) Begin
    Set @SomeVariable = SomeValue
ELSE
    Set @SomeVariable = @SomeVariable --or dummy/default value?
End

Does your code return 2 different recordsets (columns and types) based on this variable? If so, you'll have to split the stored proc into 2

Also, I found a KB article that explains why.

Edit: Change the branch into inline code...

Set @dtSomeDate = CASE WHEN ISDATE(@vcSomeDateOrAgeInDays) = 1 THEN @vcSomeDateOrAgeInDays ELSE NULL END