Optimizar consulta Transact SQL, Uso avanzado de Where, Minimizar codigo en tus sentencias SQL

El Objetivo del presente artículo es dar a conocer una forma de optimizar nuestras consultas de búsqueda al momento de crear procedimientos almacenados.

Para explicar vamos a crear un procedimiento almacenado de búsqueda con 9 parámetros en el cual vamos a poder realizar búsquedas por uno o por la combinación de los mismos; todo en una sola sentencia SELECT.

Estructura de la BD a utilizar:

Tablas: 
Alumnos:

 

Conceptos:

 

Deudas:

 

PROCEDIMIENTO ALMACENADO

    CREATE PROCEDURE [dbo].[proc_BuscarDeudas]

   
              @CodAlumno varchar(10),
              @NombAlumno varchar(150),
              @CodConcepto varchar(4),
              @AnioIni varchar(4),
              @AnioFin varchar(4),
              @CuotaIni varchar(3),
              @CuotaFin varchar(3),
              @FecVenIni datetime,
              @FecVenFin datetime
   
    AS
    BEGIN
   
      SELECT
           A.[CodAlumno],
           B.ApPaterno + ' ' + B.ApMaterno + ' ' + B.Nombres AS Alumno,
           [AnioDeu],
           [Cuota],
           A.[CodConcepto],
           C.NombConcepto,
           [DescDeuda],
           [Pension],
           [Monto],
           [Interes],
           [FecVencimiento]
      FROM [Deudas] A
      INNER JOIN Alumnos B ON A.CodAlumno=B.CodAlumno
      INNER JOIN MaesConceptos C ON A.CodConcepto=C.CodConcepto
      WHERE
        (@CodAlumno='' OR A.CodAlumno=@CodAlumno) AND
             (@NombAlumno='' OR B.ApPaterno + ' ' + B.ApMaterno + ' ' + B.Nombres LIKE '%' + @NombAlumno + '%') AND
        (@CodConcepto='' OR A.CodConcepto=@CodConcepto) AND
        (@AnioIni='' OR AnioDeu>=@AnioIni) AND
        (@AnioFin='' OR AnioDeu<=@AnioFin) AND
        (@CuotaIni='' OR Cuota>=@CuotaIni ) AND
        (@CuotaFin='' OR Cuota<=@CuotaFin) AND
        ((YEAR(@FecVenIni)='1900' AND YEAR(@FecVenFin)='1900') OR FecVencimiento BETWEEN @FecVenIni AND @FecVenFin)
   
    END

EXPLICACION:

Para conseguir nuestro objetivo haremos el uso apropiado de los aperadores lógicos OR Y AND, además necesitamos que cada uno de los parámetros de nuestro procedimiento almacenado tenga un valor de reseteo, para que así cuando no se necesite utilizar un determinado parámetro se pueda resetear enviándole dicho valor. En el caso del procedimiento almacenado que estamos utilizando en el ejemplo nuestro valores de reseteo serian '' para los parametros varchar y '1900' para los parámetro datetime.

Nuestra primera sentencia del WHERE es:
(@CodAlumno='' OR A.CodAlumno=@CodAlumno) (1)

En el caso de que enviemos como valor de reseteo '' para el parámetro @CodAlumno  la condición (1) se anularía.

Quedaría así:
(''='' OR A.CodAlumno='')

En valores lógicos seria:

(V OR A.CodAlumno='') Esta sentencia siempre será verdadera no importa el valor que devuelva A.CodAlumno='' por lo tanto toda la condición (1) es como si no existiera en el procedimiento almacenado.

En conclusión cuando no necesitemos de alguno de los parámetros del procedimiento almacenado basta con enviar el valor de reseteo para anular la condición.

Ejemplo 1:
Queremos consultar las deudas anteriores al año 2007.
La ejecución de nuestro procedimiento quedaría así:
EXEC proc_BuscarDeudas '','', '','','2007','','','01/01/1900','01/01/1900'

Ejemplo 2:
Queremos consultar las deudas de los alumnos que cuyo apellido  empiece con zapata.
La ejecución de nuestro procedimiento quedaría así:
EXEC proc_BuscarDeudas '','ZAPATA', '','','','','','01/01/1900','01/01/1900'

Ejemplo 3:
Queremos consultar las deudas del alumno cuyo código sea '0030006522' y cuota este entre 001 y 003.
La ejecución de nuestro procedimiento quedaría así:
EXEC proc_BuscarDeudas '0030006522','', '','','','001','003','01/01/1900','01/01/1900'