Optimizar filtrado en varias tablas

Por cierto, el usario prodría pedir tanto que tenga alguna de las
titulaciones especificadas como que tenga todas las titulaciones
especificadas.

El procedimiento entonces quedaría así:

ALTER PROCEDURE BuscarCandidato
@Nif varchar(50),
@Nombre varchar(50),
@Titulaciones xml,
@CondicionTitulaciones varchar(50)
AS
DECLARE @sql nvarchar(max)
SET @sql > N'SELECT IdCandidato, Nif, Nombre
FROM Candidatos
WHERE 1=1'

IF @Nif IS NOT NULL
SET @sql = @sql + N' AND Nif = @Nif'
IF @Nombre IS NOT NULL
SET @sql = @sql + ' AND Nombre = @Nombre'
IF @Titulaciones IS NOT NULL
BEGIN
IF @CondicionTitulaciones = 'Alguna'
SET @sql = @sql + N' AND IdCandidato IN
(SELECT IdCandidato
FROM TitulacionesCandidatos
WHERE IdTitulacion IN (SELECT Id.value(''.'',''int'')
FROM @Titulaciones.nodes(''//Id'') AS Titulaciones(Id)))'
ELSE
SET @sql = @sql + N' AND NOT EXISTS
(SELECT *
FROM @Titulaciones.nodes(''//Id'') AS Titulaciones(Id)
WHERE Id.value(''.'',''int'') NOT IN (SELECT IdTitulacion
FROM
TitulacionesCandidatos
WHERE IdCandidato > Candidatos.IdCandidato))'
END
exec sp_executesql @sql, N'@Nif varchar(50), @Nombre varchar(50),
@Titulaciones xml', @Nif, @Nombre, @Titulaciones
GO

Y usarlo así:

DECLARE @Titulaciones xml
SET @Titulaciones = N'<Titulaciones><Id>1</Id><Id>2</Id></Titulaciones>'
exec BuscarCandidato NULL, NULL,
N'<Titulaciones><Id>1</Id><Id>2</Id></Titulaciones>', 'Todas'

Saludos:

Jesús Lópezwww.solidqualitylearning.com

"Jesús López" escribió en el mensajenews:

> No me parece una buena idea hacer alter views para ejecutar consultas, las
> vistas en una base de datos no deberían cambiar cada vez que se hace una
> consulta, esto trae problemas de concurrencia, el usuario tiene que tener
> más permisos. Una vista debería ser más o menos permanente. Para eso sería
> mejor construir la instrucción SQL en la aplicación cliente y punto.

> Una solución es crear un procedimiento almacenado que sirva para buscar
> los candidatos sea cual sea la condición que ha elegido el usuario, el
> procedimiento puede hacer uso de ejecución dinámica.

> Por ejemplo, supongamos que tenemos las tablas:

> CREATE TABLE Candidatos
> (
> IdCandidato int IDENTITY(1,1) PRIMARY KEY,
> NIF varchar(50) UNIQUE,
> Nombre varchar(50) NOT NULL
> )
> go
> CREATE TABLE Titulaciones
> (
> IdTitulacion int IDENTITY(1,1) PRIMARY KEY,
> Titulacion varchar(50) NOT NULL,
> )
> go
> CREATE TABLE TitulacionesCandidatos
> (
> IdCandidato int NOT NULL,
> IdTitulacion int NOT NULL,
> PRIMARY KEY(IdCandidato, IdTitulacion)
> )

> Queremos un procedimiento que busque los candidatos por NIF, Nombre, o que
> tenga alguna de las Titulaciones indicadas o cualquier combinación de
> ellas combinadas con AND. Como Titulaciones pueden ser varias no lo
> podemos pasar en una varaible, necesitaríamos un array, pero no hay arrays
> en SQL. Lo que sí tenemos es XML que también nos puede servir

> Podríamos usar un procedimiento como este:

> CREATE PROCEDURE BuscarCandidato
> @Nif varchar(50),
> @Nombre varchar(50),
> @Titulaciones xml
> AS
> DECLARE @sql nvarchar(max)
> SET @sql > > N'SELECT IdCandidato, Nif, Nombre
> FROM Candidatos
> WHERE 1=1'

> IF @Nif IS NOT NULL
> SET @sql = @sql + N' AND Nif = @Nif'
> IF @Nombre IS NOT NULL
> SET @sql = @sql + ' AND Nombre = @Nombre'
> IF @Titulaciones IS NOT NULL
> SET @sql = @sql + N' AND IdCandidato IN
> (SELECT IdCandidato
> FROM TitulacionesCandidatos
> WHERE IdTitulacion IN (SELECT
> Id.value(''.'',''int'')
> FROM @Titulaciones.nodes(''//Id'') AS
> Titulaciones(Id)))'

> exec sp_executesql @sql, N'@Nif varchar(50), @Nombre varchar(50),
> @Titulaciones xml', @Nif, @Nombre, @Titulaciones
> GO

> Por ejemplo para buscar los candidatos que tengan la titulación 1 o la
> titulación 2 sería:

> exec BuscarCandidato NULL, NULL,
> N'<Titulaciones><Id>1</Id><Id>2</Id></Titulaciones>'

> Saludos:

> Jesús López

> "Juan Diego Bueno" escribió en el mensaje
>news:

> On 4 mayo, 11:01, "Jesús López" wrote:
>> Yo rediseñaría el formulario, pondría en la parte izquierda un grid que
>> muestre la lista de candidatos con sus campos más significativos y en la
>> parte derecha toda la información relativa al candidato que el usuario
>> haya
>> seleccionado en el grid. En la parte derecha puedes tener varios grids e
>> incluso fichas (tabs) que el usuario pueda seleccionar para ver la
>> información. Cuando el el usuario selecciona un candidato entonces
>> ejecutas
>> una consulta parametrizada por el NIF o Id del candidato seleccionado o
>> un
>> procedimiento almacenado que obtiene la información relativa a ese
>> candidato.

> Es que el objetivo del formulario es un filtrado general sobre los
> candidatos y sus tablas relacionadas. La idea es hacer un AND sobre
> los campos que el usuario seleccione y en los casos en los que
> proceda, permitir un OR con ocurrencias del mismo campo.

>> Lo de crear campos con múltiples valores por medio de la concatenación
>> es
>> una buena solución siempre que no sean muchos los valores a concatenar.
>> La
>> pega es la concatenación es difícil de implementar y poco eficiente (uso
>> de
>> cursores) en SQL 2000, en SQL 2005 es más sencillo y eficiente, usando
>> funciones de agregado creadas con CLR o trucos con XML. ¿qué versión
>> usas?.

> Uso SQL 2005

> Te cuento lo que he hecho al final, que creo es la solución más
> eficiente que se me ha ocurrido.

> Creo una vista sobre la tabla principal parametrizando los campos que
> no van a permitir más de una ocurrencia (filtrado por nif o por nombre
> del candidato)

> Creo una vista para cada una de las tablas relacionadas

> Ahora supongamos que el usuario ha elegido para el filtrado, dos
> titulaciones y tres puestos de trabajo de experiencia, las cuales
> están en diferentes tablas de relación. Mediante un sqlcommand, hago
> un alter view de la vista de titulaciones añadiendo el filtro que
> previamente he generado (ya que aquí no puedo usar parámetros al tener
> un cod_titulacion='tit1' or cod_titulacion='tit2'), tres cuartos de lo
> mismo con la de la experiencia (espero se me haya entendido)

> Ahora hago un alter view de la vista anterior añadiendo dos inner
> join, uno por cada una de esas vistas relacionadas

> Todavía no lo he acabado de implementar, pero debería funcionar. De
> esta forma, sólo genero las vistas que necesite y las uso en la
> consulta. Si no necesitara filtrado de ninguna de las tablas
> relacionadas, la vista principal quedaría solo con los campos
> parametrizados inicialmente.

> Tu dirás si es buena opción o no

> Saludos