Consultas dinámicas en Sqlserver 2000

Hace algún tiempo atrás tuve que implementar en la aplicación que estuve desarrollando, consultas configurables para que el usuario puede filtrar la información de acuerdo a su necesidad, pongamos como ejemplo a la base de datos Northwind y la tabla Products, la aplicación debía consultar por cualquier columna que el usuario seleccione y filtrar la información según las coincidencias encontradas.

A mi no me pareció complejo hacer algo asi, porque solo debía armar la consulta en la aplicación y ejecutarla, pero luego me di cuenta que hacerlo de esta manera tenia dos desventajas:

- Al armar las consultas en la aplicación, esta se convertía en poco escalable y muy estática porque cualquier cambio o actualización que quisiese hacer tenia que modificar el código del programa y crear otro instalador o en todo caso reemplazar la dll donde se ha realizado el cambio.

- Las consultas que se envían desde la aplicación consumen mas recursos del servidor porque sql server tiene que cargar en memoria la sentencia a ejecutar, luego analizarla, compilarla y por ultimo ejecutarla, ocasionando que el servidor consuma mas recursos.

Los procedimientos almacenados están pre compilados en el servidor, por lo cual ejecutarlos tiene un menor costo para el servidor.

Para no armar y concatenar la sentencia en la aplicación debía ser capaz de armar la consulta en el servidor a travez de un procedimiento almacenado.

Para mi satisfacción encontré que desde la versión Sql Server 2000 existe el comando sp_executesql, capaz de ejecutar cualquier tipo de consulta que se le especifique.

El siguiente ejemplo usa sp_executesql y consiste en crear un procedimiento almacenado que consulte a la tabla Products sujeto a la condición que se envia al procedimiento.

GO
drop function [fGetCondicion]
GO
create function [dbo].[fGetCondicion]
(
@Condicion varchar(20),
@value varchar(100)
)
RETURNS  VARCHAR(20)
AS
-- =============================================
-- Author:  Milton Baltazar Valenzuela
-- Description: Obtiene el tipo de condicion y el valor
--       de la condicion para devolver la condicion
--       en el formato requerido
-- =============================================
BEGIN
RETURN (
SELECT
CASE @Condicion
WHEN 'like' THEN @Condicion + '''%' + @value + '%'''
WHEN '='  THEN @Condicion + ' ' + @value
WHEN '<>' THEN @Condicion + ' ' + @value
WHEN '>'  THEN @Condicion + ' ' + @value
WHEN '<'  THEN @Condicion + ' ' + @value  ELSE null END) END GO  GO drop procedure usp_get_products GO create procedure usp_get_products (   @productID            int  ,@ProductName          varchar(40)  ,@CategoryID           int  ,@UnitPrice            money  ,@Condicion     varchar(100) ) AS BEGIN Declare @ls_query nvarchar(4000) Declare @li_error int Declare @ls_error  varchar(1000)   set @ls_query='select productID, ProductName, CategoryID, UnitPrice from dbo.Products'     if @productID is not null    begin      set @ls_query = @ls_query + ' where productID ' + dbo.fGetCondicion(@Condicion,@productID)  end   else if @ProductName is not null    begin      set @ls_query = @ls_query + ' where ProductName ' + dbo.fGetCondicion(@Condicion,@ProductName)  end   else if @CategoryID is not null    begin      set @ls_query = @ls_query + ' where CategoryID ' + dbo.fGetCondicion(@Condicion,@CategoryID)  end        exec @li_error = sp_executesql   @ls_query,N' @productID            int     ,@ProductName          varchar(40)     ,@CategoryID           int     ,@Condicion      varchar(100)'     ,@productID       ,@ProductName      ,@CategoryID       ,@Condicion ;      if @li_error <> 0
begin
set @ls_error = 'Error en la consulta. '
RaisError(@ls_error,16,1)
end
END
GO


----------------------------------------------------
--ejecutamos el procedimiento almacenado
----------------------------------------------------

exec usp_get_products 10,null,null,null,'='

exec usp_get_products null,'',null,null,'like'

exec usp_get_products null,'Chai',null,null,'Like'


GO

En resumen he creado un procedimiento almacenado que me permita consultar
cualquier campo de acuerdo a la condición que le envío, es cierto que al código no esta al 100% perfeccionado pero solo quiero dar la idea de que se puede hacer con sp_executesql.