SQL Server 2005: "Proteger" procedimientos almacenados del modo FMTONLY utilizado por MS Access



sql-server ms-access (1)

¿Qué hay de esto?

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

¿Su código devuelve 2 conjuntos de registros diferentes (columnas y tipos) basados ​​en esta variable? Si es así, tendrás que dividir el proceso almacenado en 2

Además, encontré un artículo de KB que explica por qué.

Editar: cambie la rama en código en línea ...

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

Algunos de los procedimientos almacenados que tenemos contienen lógica condicional, como esta:

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

Cuando dicho procedimiento almacenado se usa como un origen de registros para un formulario de MS Access, y el usuario intenta utilizar la funcionalidad integrada de clasificación / filtrado del formulario, MS Access intenta ejecutar el procedimiento almacenado en modo FMTONLY (aparentemente, buscando metadatos de el conjunto de filas proporcionado por el procedimiento almacenado).

Como la mayoría de la gente sabe (ahora incluyéndonos a nosotros mismos :-), cuando FMTONLY está activado, SQL Server ignora las declaraciones condicionales. En el ejemplo que se muestra a continuación, la Set @SomeVariable = SomeValue se ejecuta independientemente de si Some Condition es true, lo que obviamente nos crea algunos problemas.

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

Para eludir este problema, "envolvemos" la lógica condicional (o cualquier otro fragmento de código afectado por FMTONLY) de esta manera:

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

(Este feo formato de una línea del "código de protección" es intencional: creemos que los hacks necesarios para resolver algunos problemas extraños no merecen un formato adecuado, sino todo lo contrario, creemos que deben caber en las pocas líneas de código posibles . :-)

De todos modos, esta "protección" funciona bien, pero es algo demasiado detallada y no tan encapsulada como quisiéramos que fuera. Por ejemplo, preferiríamos ocultar la lógica real del hack, por ejemplo, detrás de un UDF escalar como este:

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

Desafortunadamente, esto no parece funcionar, ni con las UDF escalares, ni con otro procedimiento almacenado. Parece que FMTONLY impide el retorno de datos desde cualquier lugar. Entonces, aquí viene la pregunta principal :

Si también tuvo que lidiar con este problema (SQL Server ignorando los condicionales en el modo FMTONLY), ¿fue capaz de encontrar un mejor "idioma de protección" que el descrito anteriormente?

Por cierto, todavía no entiendo una cosa: ¿este problema es un error o una característica en SQL Server 2005? Y si es una característica, ¿cuál podría ser una buena razón para ello?

¡Gracias!





coding-style