SQL - Store Procedure con parametros optativos en el where

Store Procedure con parametros optativos en el where
Posted jueves, 16 de marzo de 2006 12:24 por Carlos Fouz
¿Como generar un store procedure que puede recibir parametros opcionales que esten en el where de la sentencia sin incharnos ha hacer if y chapuzas varias?
aqui va un ejemplo :
CREATE  PROCEDURE  Cliente
@numcli varchar(100) = null,
@nombre varchar(500) = null,
@domiciliovarchar(500) = null
AS
SELECT
                      ID ,
                      NumeroCliente,
                      NombreCliente,
                      CIF,
                      Domicilio,
                      Telefono
FROM
                      Cliente
WHERE
    ((@numcli Is Null) Or (NumeroCliente= @numcli))
And ((@nombre Is Null) Or (NombreCliente= @nombre))
And ((@domicilio IsNull) Or (Domicilio= @domicilio))

ORDER BY NumeroCliente
GO
Parece una chorrada y lo que me costó pensarlo jejeje.
Lo único que no puedo asegurar es que la condición del where tipo ((@numcli Is Null) Or (NUMCLI = @numcli)) se ejecute como Lazy Conditional , es decir, si la primera ya es cierta no mire la segunda expersión ya que es un or y no influiria en el resultado final de la expersion. Sialguien sabe si sql server realiza lazy conditional , please post me :)
Archivado en: sql server
Comentarios
# re: Store Procedure con parametros optativos en el where
miércoles, 22 de marzo de 2006 23:23 by SqlRanger
Siento desilusionarte, pero esa técnica que propones es muy ineficiente. La razón es que SQL Server no es lo suficientemente listo para crear un plan de ejecución óptimo, en sulugar hace una exploración completa de la tabla o de alguno de los índices. Pero lo peor no es esto, sino que el plan de ejecución que se crea la primera vez que se ejecuta el procedimiento se utiliza para las demás veces. Y este  plan de ejecución debería ser muy distinto con diferentes combinaciones de parámetros con valores nulos.

Por ejemplo en la base de datos AdventureWorks. Supongamosque creamos el siguiente índice para acelerar búsquedas sobre LastName en la tabla Person.Contact:

USE [AdventureWorks]
GO
CREATE INDEX [Contact_LastName] ON [Person].[Contact]
(
[LastName],
[ContactID]
)
INCLUDE ( [FirstName],
[MiddleName],
[EmailAddress],
[Phone])


Y el siguiente procedimiento almacenado:

CREATE PROCEDURE GetContact
@ContactID int,
@LastNamenvarchar(50)
AS
SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone
FROM Person.Contact
WHERE (@ContactID IS NULL OR ContactID = @ContactID) AND
 (@LastName IS NULL OR LastName = @LastName)

Si ejecutamos lo siguiente:

SET STATISTICS IO ON
GO

DECLARE @ContactID int
SET @ContactID = 2000
-- sentencia 1
EXEC GetContact @ContactID, NULL

-- sentencia2
SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone
FROM Person.Contact
WHERE ContactID = @ContactID

DECLARE @LastName nvarchar(50)
SET @LastName = N'Romero'

-- sentencia 3
EXEC GetContact NULL, @LastName

-- sentencia 4
SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone
FROM Person.Contact
WHERE LastName = @LastName

Enprincipio parecería que la ejecución de la sentencia 1 sería similar a la ejecución de la sentencia 2 y la ejecución de la sentencia 3 similar a la ejecución de la sentencia 4. Sin embargo:

La sentencia 1 hace 61 lecturas lóginas
La sentencia 2 hace 2 lecturas lógicas
La sentencia 3 hace 40003 lecturas lóginas
La sentencia 4 hace 6 lecturas lógicas

Lo cual demuestra que el procedimientoalmacenado es terriblemente ineficiente comparado con las sentencias SELECT que hacen lo mismo.

Saludos:

Jesús López
MVP
# re: Store Procedure con parametros optativos en el where
viernes, 24 de marzo de 2006 16:15 by Carlos Fouz
Muchas gracias Jesús López, entiendo perfectamente tu razonamiento y sobretodo :

La sentencia 3 hace 40003 lecturas lóginas
La sentencia 4 hace 6...