Top Panel

An example of a static store procedure that use dynamic filters

Details
Created on Friday, 05 August 2011 16:32
Category: MS SQL Server
Hits: 80
  • Print
SocButtons plugin

Need an example of a static store procedure that use dynamic filters? [filters to search information for the user, that will be used in the query if they have information, if don't, that filters are disabled or at least the workaround they have to avoid the dynamic procedures?]

It may be because there is a client requirement where the client says something like "you can't use variables in the store procedures"

The key is in the where clause, let's say that you want to get a list of employees based on fields:

id_empresa, id_cargo, id_departamento, nro_personal and id_proceso, but you don't know what filter is the user going to choose, could be one or all, the query will look like this:

select *
from ph_empleados ph_e
where ph_e.id_empresa=isnull(@pr_id_empresa,ph_e.id_empresa)
and ph_e.id_cargo=isnull(@pr_id_cargo,ph_e.id_cargo)
and ph_e.id_departamento=isnull(@pr_id_departamento,ph_e.id_departamento)
and ph_e.nro_personal=isnull(@pr_nro_personal,ph_e.nro_personal)
and ph_e_p.id_proceso=isnull(@pr_id_proceso,ph_e_p.id_proceso)
If the user didn't choose a filter then the variable that contains the value will be null, and if that's the case the data is not going to be filter by that one