Multiples Filtros, Where con Case contra Sql Dinamico

Pablodegerli,

La primera opcion definitivamente no, porque evitara que sql server haga un
uso efectivo de los indices. Aca te paso un ejemplo para que veas como sql
server decide hacer un "index scan" en vez de un "index seek" en el indice
"companyname" para la primera sentencia, no asi para la segunda.

Ejemplo:

use northwind
go

set statistics profile on
go

declare @s nvarchar(40)

select
*
from
dbo.customers
where
companyname = coalesce(@s, companyname)

select
*
from
dbo.customers
where
companyname like coalesce(@s, '%')
go

set statistics profile off
go

La segunda opcion puede ser factible si no es un inconveniente darle permiso
de lectura al usuario sobre la tabla en quetion, en vez de darle solo permiso
de ejecucion sobre procedimiento almacenado. Tambien debes tener mucho
cuidado con la inyeccion de codigo t-sql hacia sql server. Esta opcion hara
que sql server ejecute la sentencia en un nuevo contexto, creando o reusando
un nuevo plan de ejecucion y ademas trabajara con un numero mas reducido de
columnas en la clausula "where".

Otra solucion para este caso especifico (solo tienes parametros varchar), es
usar el operador "like" junto con la funcion isnull o coalesce. Si te fijas
bien en el script anterior, notaras que la segunda sentencia usa este patron
y como podras ver, sql server hace un uso optimo del indice.


AMB



"Pablodegerli" wrote:

Mostrar la cita

Hola gente
Quisiera saber que opinan de esto, necesito hacer una consulta con varios
filtros que pueden o no ser enviados a la misma, y para ello encontre 2
alternativas, usar un where con un case por cada paraetro o armar un sql
dinamico.
Ejemplo

Create Table TestSelect (Id_Pk int identity(1,1) ,Campo1 varchar(5), Campo2
varchar(5), Campo3 varchar(5))

Create Procedure Test1 @Parametro1 varchar(5) = null, @Parametro2
varchar(5) = null, @Parametro3 varchar(5) = null
as
Select * from TestSelect
where Campo1 = (Case when @Parametro1 is null then Campo1 else
@Parametro1 end)
and Campo2 = (Case when @Parametro2 is null then Campo2 else
@Parametro2 end)
and Campo3 = (Case when @Parametro3 is null then Campo3 else
@Parametro3 end)

O

Create Procedure Test2 @Parametro1 varchar(5) = null, @Parametro2
varchar(5) = null, @Parametro3 varchar(5) = null
as
Declare @Sql nvarchar(4000), @W varchar(20)

set @Sql = 'Select * from TestSelect '
set @W = ' where'
if not @Parametro1 is null
begin
set @Sql = @Sql + @W + ' Campo1 = '''+ @Parametro1 + ''''
set @W = ' and '
end
if not @Parametro2 is null
begin
set @Sql = @Sql + @W + ' Campo2 = '''+ @Parametro2 + ''''
set @W = ' and '
end
if not @Parametro3 is null
begin
set @Sql = @Sql + @W + ' Campo3 = '''+ @Parametro3 + ''''
set @W = ' and '
end
exec Sp_ExecuteSql @Sql

Además de lo que te comenta Alejandro, echa un vistazo a este artículo:
http://www.sommarskog.se/dyn-search.html


Un saludo

-
"Sólo sé que no sé nada. " (Sócrates)

"Pablodegerli" escribió en el mensaje
news:
Mostrar la cita

Hola gente
Quisiera saber que opinan de esto, necesito hacer una consulta con varios
filtros que pueden o no ser enviados a la misma, y para ello encontre 2
alternativas, usar un where con un case por cada paraetro o armar un sql
dinamico.
Ejemplo

Create Table TestSelect (Id_Pk int identity(1,1) ,Campo1 varchar(5),

Campo2
Mostrar la cita

varchar(5), Campo3 varchar(5))

Create Procedure Test1 @Parametro1 varchar(5) = null, @Parametro2
varchar(5) = null, @Parametro3 varchar(5) = null
as
Select * from TestSelect
where Campo1 = (Case when @Parametro1 is null then Campo1 else
@Parametro1 end)
and Campo2 = (Case when @Parametro2 is null then Campo2 else
@Parametro2 end)
and Campo3 = (Case when @Parametro3 is null then Campo3 else
@Parametro3 end)

O

Create Procedure Test2 @Parametro1 varchar(5) = null, @Parametro2
varchar(5) = null, @Parametro3 varchar(5) = null
as
Declare @Sql nvarchar(4000), @W varchar(20)

set @Sql = 'Select * from TestSelect '
set @W = ' where'
if not @Parametro1 is null
begin
set @Sql = @Sql + @W + ' Campo1 = '''+ @Parametro1 + ''''
set @W = ' and '
end
if not @Parametro2 is null
begin
set @Sql = @Sql + @W + ' Campo2 = '''+ @Parametro2 + ''''
set @W = ' and '
end
if not @Parametro3 is null
begin
set @Sql = @Sql + @W + ' Campo3 = '''+ @Parametro3 + ''''
set @W = ' and '
end
exec Sp_ExecuteSql @Sql

Muchisimas Gracias
Estoy viendo sus respuestas, como siempre es un lujo.

"Pablodegerli" escribió en el mensaje
news:
Mostrar la cita

Hola gente
Quisiera saber que opinan de esto, necesito hacer una consulta con varios
filtros que pueden o no ser enviados a la misma, y para ello encontre 2
alternativas, usar un where con un case por cada paraetro o armar un sql
dinamico.
Ejemplo

Create Table TestSelect (Id_Pk int identity(1,1) ,Campo1 varchar(5),

Campo2
Mostrar la cita

varchar(5), Campo3 varchar(5))

Create Procedure Test1 @Parametro1 varchar(5) = null, @Parametro2
varchar(5) = null, @Parametro3 varchar(5) = null
as
Select * from TestSelect
where Campo1 = (Case when @Parametro1 is null then Campo1 else
@Parametro1 end)
and Campo2 = (Case when @Parametro2 is null then Campo2 else
@Parametro2 end)
and Campo3 = (Case when @Parametro3 is null then Campo3 else
@Parametro3 end)

O

Create Procedure Test2 @Parametro1 varchar(5) = null, @Parametro2
varchar(5) = null, @Parametro3 varchar(5) = null
as
Declare @Sql nvarchar(4000), @W varchar(20)

set @Sql = 'Select * from TestSelect '
set @W = ' where'
if not @Parametro1 is null
begin
set @Sql = @Sql + @W + ' Campo1 = '''+ @Parametro1 + ''''
set @W = ' and '
end
if not @Parametro2 is null
begin
set @Sql = @Sql + @W + ' Campo2 = '''+ @Parametro2 + ''''
set @W = ' and '
end
if not @Parametro3 is null
begin
set @Sql = @Sql + @W + ' Campo3 = '''+ @Parametro3 + ''''
set @W = ' and '
end
exec Sp_ExecuteSql @Sql

Carlos,

No se como se olvido adjuntar ese link, de verdad que sus articulos estan
entre mis favoritos. Que bueno que es trabajar en grupo.


AMB

"Carlos Sacristán" wrote:

Mostrar la cita

Además de lo que te comenta Alejandro, echa un vistazo a este artículo:
http://www.sommarskog.se/dyn-search.html


Un saludo

-
"Sólo sé que no sé nada. " (Sócrates)

"Pablodegerli" escribió en el mensaje
news:
> Hola gente
> Quisiera saber que opinan de esto, necesito hacer una consulta con varios
> filtros que pueden o no ser enviados a la misma, y para ello encontre 2
> alternativas, usar un where con un case por cada paraetro o armar un sql
> dinamico.
> Ejemplo
>
> Create Table TestSelect (Id_Pk int identity(1,1) ,Campo1 varchar(5),
Campo2
> varchar(5), Campo3 varchar(5))
>
> Create Procedure Test1 @Parametro1 varchar(5) = null, @Parametro2
> varchar(5) = null, @Parametro3 varchar(5) = null
> as
> Select * from TestSelect
> where Campo1 = (Case when @Parametro1 is null then Campo1 else
> @Parametro1 end)
> and Campo2 = (Case when @Parametro2 is null then Campo2 else
> @Parametro2 end)
> and Campo3 = (Case when @Parametro3 is null then Campo3 else
> @Parametro3 end)
>
> O
>
> Create Procedure Test2 @Parametro1 varchar(5) = null, @Parametro2
> varchar(5) = null, @Parametro3 varchar(5) = null
> as
> Declare @Sql nvarchar(4000), @W varchar(20)
>
> set @Sql = 'Select * from TestSelect '
> set @W = ' where'
> if not @Parametro1 is null
> begin
> set @Sql = @Sql + @W + ' Campo1 = '''+ @Parametro1 + ''''
> set @W = ' and '
> end
> if not @Parametro2 is null
> begin
> set @Sql = @Sql + @W + ' Campo2 = '''+ @Parametro2 + ''''
> set @W = ' and '
> end
> if not @Parametro3 is null
> begin
> set @Sql = @Sql + @W + ' Campo3 = '''+ @Parametro3 + ''''
> set @W = ' and '
> end
> exec Sp_ExecuteSql @Sql
>
>

Está bien haberte pillado en alguna, Alejandro ;-)


Un saludo

-
"Sólo sé que no sé nada. " (Sócrates)

"Alejandro Mesa" escribió en el
mensaje news:
Mostrar la cita

Carlos,

No se como se olvido adjuntar ese link, de verdad que sus articulos estan
entre mis favoritos. Que bueno que es trabajar en grupo.


AMB

"Carlos Sacristán" wrote:

> Además de lo que te comenta Alejandro, echa un vistazo a este

artículo:
Mostrar la cita

> http://www.sommarskog.se/dyn-search.html
>
>
> Un saludo
>
> -
> "Sólo sé que no sé nada. " (Sócrates)
>
> "Pablodegerli" escribió en el mensaje
> news:
> > Hola gente
> > Quisiera saber que opinan de esto, necesito hacer una consulta con

varios
Mostrar la cita

> > filtros que pueden o no ser enviados a la misma, y para ello encontre

2
Mostrar la cita

> > alternativas, usar un where con un case por cada paraetro o armar un

sql
Mostrar la cita

> > dinamico.
> > Ejemplo
> >
> > Create Table TestSelect (Id_Pk int identity(1,1) ,Campo1 varchar(5),
> Campo2
> > varchar(5), Campo3 varchar(5))
> >
> > Create Procedure Test1 @Parametro1 varchar(5) = null, @Parametro2
> > varchar(5) = null, @Parametro3 varchar(5) = null
> > as
> > Select * from TestSelect
> > where Campo1 = (Case when @Parametro1 is null then Campo1 else
> > @Parametro1 end)
> > and Campo2 = (Case when @Parametro2 is null then Campo2 else
> > @Parametro2 end)
> > and Campo3 = (Case when @Parametro3 is null then Campo3 else
> > @Parametro3 end)
> >
> > O
> >
> > Create Procedure Test2 @Parametro1 varchar(5) = null, @Parametro2
> > varchar(5) = null, @Parametro3 varchar(5) = null
> > as
> > Declare @Sql nvarchar(4000), @W varchar(20)
> >
> > set @Sql = 'Select * from TestSelect '
> > set @W = ' where'
> > if not @Parametro1 is null
> > begin
> > set @Sql = @Sql + @W + ' Campo1 = '''+ @Parametro1 + ''''
> > set @W = ' and '
> > end
> > if not @Parametro2 is null
> > begin
> > set @Sql = @Sql + @W + ' Campo2 = '''+ @Parametro2 + ''''
> > set @W = ' and '
> > end
> > if not @Parametro3 is null
> > begin
> > set @Sql = @Sql + @W + ' Campo3 = '''+ @Parametro3 + ''''
> > set @W = ' and '
> > end
> > exec Sp_ExecuteSql @Sql
> >
> >
>
>
>