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:

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