SQL Server 2005：“保护”存储过程免受MS Access使用的FMTONLY模式的影响

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).

当这样的存储过程用作MS Access表单的记录源,并且用户尝试使用表单的内置排序/过滤功能时,MS Access尝试以FMTONLY模式执行存储过程(显然,寻找元数据)存储过程提供的行集)。

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.

正如大多数人所知(现在包括我们自己:-),当FMTONLY设置为ON时,SQL Server会忽略条件语句。在下面显示的示例中,无论Some Condition是否为true,都会执行Set @SomeVariable = SomeValue语句,这显然会给我们带来一些麻烦。

-- 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:

为了避免这个问题,我们“包装”条件逻辑(或受FMTONLY影响的任何其他代码片段),如下所示:

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:

无论如何,这种“保护”工作正常,但它有点过于冗长,并没有像我们希望的那样多封装。例如,我们肯定更喜欢隐藏黑客的实际逻辑 - 例如,在这样的标量UDF后面:

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:

不幸的是,这似乎不起作用 - 既不使用标量UDF,也不使用其他存储过程。看起来FMTONLY阻止从任何地方返回任何数据。所以,主要问题是:

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?

如果你还必须处理这个问题(SQL Server忽略FMTONLY模式下的条件),你是否能够提出一个比上述更好的“保护习语”?

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?

顺便说一句,我仍然不明白一件事:这个问题是SQL Server 2005中的错误还是一个功能?如果它是一个功能,那么它可能是一个很好的理由呢?

Thank you!

1 个解决方案

#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

您的代码是否基于此变量返回2个不同的记录集(列和类型)?如果是这样,您将必须将存储的proc拆分为2

Also, I found a KB article that explains why.

另外,我发现了一篇KB文章解释了原因。

Edit: Change the branch into inline code...

编辑:将分支更改为内联代码...

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

#1