viernes, octubre 21, 2011

SQL Server: una forma rapida de crear un IN dinamico

Esta es una situación que seguro les ha pasado muchas veces. Tienen un SP que recibe un parametro usado para filtrar sobre una tabla, digamos @status. Normalmente haríamos algo como esto:

SELECT * 
  FROM tabla
 WHERE (@status IS NULL OR status = @status)


Eso nos permitiria filtrar por un status especifico o no filtrar por status, indicando el valor NULL. Pero luego nos topamos con una situación en la que necesitamos filtrar por DOS valores de status distintos, digamos, registros ACTIVOS y ANULADOS. Obviamente nuestra primera intención es hacer:

SELECT * 
  FROM tabla
 WHERE (@status IS NULL OR status IN (@status))


lo cual no es una instrucción valida para SQL Server, sin importar lo logico que se vea. Normalmente la solución a esta situación pasa por la creación de una función que tome un VARCHAR con la lista de valores separados por coma y devuelva un TABLE con los valores ya separados, y luego usamos ese TABLE para hacer un FULL JOIN o un IN (SELECT).

Pero hoy encontre una solución bien sencilla, usando las desconocidas (al menos para mi) capacidades XML de SQL Server. La idea, basicamente, es usar la lista de valores para crear un XML y luego usar ese XML como una fuente datos en un IN (SELECT). Aplicando esto al ejemplo que mencionaba al principio, la cosa quedaria asi:

DECLARE @x XML
SET @x = '' + REPLACE( @status, ',', '') + ''

SELECT * 
  FROM tabla
 WHERE status IN (SELECT x.j.value('.', 'VARCHAR(max)') AS item FROM @x.nodes('//j') x(j))


Simple, cierto?

6 comentarios:

juanziur dijo...

Cuando intento ejecutar el ejemplo, me da un error, "Debe declarar la variable escalar "@status"." y no encuentro la forma o el tipo de dato que debo usar, ojala me pudieras ayudar, desde ya Gracias

Victor Espina dijo...

Hola. El código que aparece en el post no es funcional; es solo para ilustrar el concepto.

SatanCloS dijo...

interesante, para aportar algo, yo trabajo los IN dinamicos dentro de un procedimiento q me contralka un array
es decir

select *
from tabla
where status in (usp_array_codigos(codigos)as status character varying(30))


Saludos Peru_Male

Walter Valle dijo...

Genial... Ya lo estoy utilizando en la "vida real" :). Gracias amigo!.

Walter Valle dijo...

Hola Víctor,

Otra solución bastante simple y que nos ayuda mucho a los que en algunos casos usamos SQL Dinámico:

El supuesto parametro @pcStatus contendría una cadena como esta: ' ,S,P,A'

DECLARE @pcStatus VarChar(100), @cStatus VarChar(100)

SET @pcStatus = ' ,S,P,A'

SET @cStatus = CHAR(39) + REPLACE(@pcStatus, ',', CHAR(39) + ',' + CHAR(39)) + CHAR(39)

PRINT @cStatus

Es una solución mas que en algún momento puede servir :)

Saludos!

Edwin Jimenez dijo...

Excelente post, lo utilice y funciona perfectamente !!