En ocasiones necesitamos escribir un stored procedure que recibirá varios parámetros de entrada pero cuando llamamos a ese stored procedure la cantidad de parámetros que le enviamos puede variar, no es siempre la misma cantidad y ni siquiera sabemos de antemano cuales y cuantos parámetros necesitaremos.

¿Podemos escribir un stored procedure que acepte una cantidad variable de parámetros, existe una forma de hacerlo?

Ejemplo: Necesitamos consultar a una tabla de VENDEDORES pero a veces el dato que conocemos es su Identificador, a veces es el Nombre, y a veces es la Fecha de Nacimiento.

SP01

Captura 1. Si haces clic en la imagen la verás más grande

Listado 1.

CREATE PROCEDURE SP_CONSULTAR_VENDEDORES (
   tnParam1 SMALLINT,
   tcParam2 VARCHAR(40),
   tdParam3 DATE)
RETURNS(
   ftnIdenti TYPE OF COLUMN VENDEDORES.VEN_IDENTI,
   ftcNombre TYPE OF COLUMN VENDEDORES.VEN_NOMBRE,
   ftdFecNac TYPE OF COLUMN VENDEDORES.VEN_FECNAC)
AS
   DECLARE VARIABLE lnValor1 SMALLINT;
   DECLARE VARIABLE lcValor2 VARCHAR(40);
   DECLARE VARIABLE ldValor3 DATE;
BEGIN

   FOR SELECT
      VEN_IDENTI,
      VEN_NOMBRE,
      VEN_FECNAC
   FROM
      VENDEDORES
   WHERE
      VEN_IDENTI = COALESCE(:tnParam1, VEN_IDENTI) AND
      VEN_NOMBRE = COALESCE(:tcParam2, VEN_NOMBRE) AND
      VEN_FECNAC = COALESCE(:tdParam3, VEN_FECNAC)
   INTO
      :lnValor1,
      :lcValor2,
      :ldValor3
   DO BEGIN
      ftnIdenti = lnValor1;
      ftcNombre = lcValor2;
      ftdFecNac = ldValor3;
      SUSPEND;
   END

END;

En este caso se trata de un stored procedure seleccionable pero exactamente la misma lógica usaríamos si se tratara de un stored procedure ejecutable. OBSERVACIÓN: en un stored procedure tan simple como este no es necesario usar variables locales, podríamos haber usado solamente los parámetros de salida, pero se muestra el caso general que puede ser más útil.

Entonces, podríamos escribir algo como:

Listado 2.

SELECT
   *
FROM
   SP_CONSULTAR_VENDEDORES(NULL, 'CLAUDIA', NULL)


Y obtendríamos lo siguiente:

SP02

Captura 2. Si haces clic en la imagen la verás más grande

Y si escribimos algo como:

Listado 3.

SELECT
   *
FROM
   SP_CONSULTAR_VENDEDORES(NULL, NULL, CAST('30/OCT/1994' AS DATE))

entonces obtendríamos lo siguiente:

SP03

Captura 3. Si haces clic en la imagen la verás más grande

Como ves, los parámetros desconocidos o innecesarios completamos con NULL, porque este stored procedure debe recibir sí o sí 3 parámetros de entrada.

Conclusión:

Si necesitamos varios stored procedures que son muy similares y la única diferencia entre ellos está en los parámetros de entrada entonces lo más conveniente es escribir un solo stored procedure y al llamarlo hacerlo con el parámetro o los parámetros adecuados en ese momento.

El truco está en usar la función COALESCE() para saber si un parámetro de entrada es NULL o tiene un valor distinto que NULL. Así sabremos si se le asignó un valor, o no.

Artículos relacionados:

La función COALESCE()

El índice del blog Firebird21

El foro del blog Firebird21