ORDER BY dinámico en MS SQL 2000... o como hacerte mas bolas con un CASE

 

Durante años he programados consultas en SQL, y a mucha honra puedo decir que cada vez me quedan menos peor. Tal vez no estarán a la altura de las consultas realizadas por nuestro DBA estrella de la chamba, pero el 99% de mis queries hacen lo que deben de hacer!

 

De las cosas mas complicadas en cuestión de queries que me ha tocado en MS SQL es el realizar consultas dinámicas dentro de un SP. Lo normal o lo fácil es realizar un Stored Procedure donde se guarde el negocio del “armado” del query y después se ejecute mediante un EXEC pero vamos a ver, pa’ andar haciendo eso mejor armo el query en la capa DAL y no mando ningún SP, el punto de un SP es hacer trabajar lo menos posible al motor de la DB, y EXEC no mas no sirve pa eso!

 

Consideremos el siguiente caso, tenemos un sistema que guarda un log cada vez que un usuario inicia sesión, tenemos dos tablas:

 

 

Y queremos crear un SP que nos permita saber estadísticas del log de inicio de sesiones, sobretodo queremos saber cuantas veces a entrado al aplicativo cada usuario.

 

Usamos el siguiente query para crear el SP:

 

 

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spLoginStats]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[spLoginStats]

GO

 

/*********************************************************************

AUTOR ToraValenzuela

DESC: Obtenen las estadisticas de cuantas veces

se ha logeado un usuario en la app

*********************************************************************/

CREATE PROCEDURE dbo.spLoginStats

AS

BEGIN

 

SELECT u.id_usuario,

u.usuario,

u.departamento,

COUNT(l.id_login) AS total_de_logins -- Total de logins en el sistema por cada usuario

FROM dbo.DEMO_USUARIOS u

LEFT OUTER JOIN dbo.DEMO_LOGINS l -- OUTER JOIN ya que puede haber usuarios

on l.id_usuario = u.id_usuario -- que nunca se han logeado en el sistema

GROUP BY u.id_usuario,

u.usuario,

u.departamento

 

END

GO

 

Al ejecutarlo nos da los siguientes resultados

 

EXEC dbo.spLoginStats

 

id_usuario usuario departamento total_de_logins

----------- -------------------- ------------ ---------------

1 toravalenzuela IT 10

2 vendedor VENTAS 6

3 admin IT 3

4 supervisor VENTAS 6

5 alguien COBROS 0

6 YoNuncaEntro DIRECCION 0

No rows affected.

 

 

 

 

Ora supongamos que queremos ordenar los resultados, mas bien nuestro usuario nos dice que quiere poder ordenar los resultados en 3 diferentes formas:

1) por nombre de usuario

2) por nombre de departamento

3) por total de inicios de sesión de mayor a menor

 

 

Podríamos dejarle a la GUI/BLL de la aplicación esta tarea, pero queremos ordenarlos desde Base de Datos, dependiendo de un parámetro que le pasemos al SP.

 

Aquí es donde se complica la cosa, podríamos hacer un query dinámico dentro del SP y que sea ejecutado por EXEC, o que el SP escoja entre varios queries hardcodeados, pero vamos a hacer un ORDER BY dinámico.

 

Le vamos a incluir el parámetro @ORDER_BY al SP, este va a ser un INT y de este va a depender el orden a utilizar.

 

Modificamos en SP de la siguiente manera.

 

 

/*********************************************************************

AUTOR ToraValenzuela

DESC: Obtenen las estadisticas de cuantas veces

se ha logeado un usuario en la app

@ORDER_BY 0 ordenar por nombre de usuario

1 ordenar por nombre de departamento

2 ordenar por total de inicios de sesión de mayor a menor

*********************************************************************/

ALTER PROCEDURE dbo.spLoginStats

@ORDER_BY int = 0

AS

BEGIN

 

SELECT u.id_usuario,

u.usuario,

u.departamento,

COUNT(l.id_login) AS total_de_logins, -- Total de logins en el sistema por cada usuario

-- Creamos una columna/alias "ORDER BY" que nos va a servir de apoyo para ordenar

CASE (@ORDER_BY)

WHEN 0 THEN u.usuario

WHEN 1 THEN u.departamento

ELSE COUNT(l.id_login)

END AS ORDER_BY

FROM dbo.DEMO_USUARIOS u

LEFT OUTER JOIN dbo.DEMO_LOGINS l -- OUTER JOIN ya que puede haber usuarios

on l.id_usuario = u.id_usuario -- que nunca se han logeado en el sistema

GROUP BY u.id_usuario,

u.usuario,

u.departamento

ORDER BY ORDER_BY

 

END

GO

 

 

Ya añadimos el parámetro y le dejamos un default (0).

Mediante un CASE del parámetro seleccionamos una de tres columnas y le ponemos el alias de ORDER_BY.

Añadimos la cláusula ORDER BY ORDER_BY.

 

Al ejecutar el SP nos aparece el siguiente error

 

EXEC dbo.spLoginStats 0

GO

 

Error de sintaxis al convertir el valor varchar 'toravalenzuela' para una columna de tipo de datos int.

 

 

Aquí empiezan nuestros problemas, COUNT(l.id_login) es INT, las otras dos varchar, así que vamos a realizar un CAST en el SP

 

 

/******************************************************************************

AUTOR ToraValenzuela

DESC: Obtenen las estadisticas de cuantas veces

se ha logeado un usuario en la app

@ORDER_BY 0 ordenar por nombre de usuario

1 ordenar por nombre de departamento

2 ordenar por total de inicios de sesión de mayor a menor

MODIFICACIONES - Realiza CAST a VARCHAR en el CASE

******************************************************************************/

ALTER PROCEDURE dbo.spLoginStats

@ORDER_BY int = 0

AS

BEGIN

 

SELECT u.id_usuario,

u.usuario,

u.departamento,

COUNT(l.id_login) AS total_de_logins, -- Total de logins en el sistema por cada usuario

-- Creamos una columna que nos va a servir de apoyo para ordenar

CASE (@ORDER_BY)

WHEN 0 THEN u.usuario

WHEN 1 THEN u.departamento

ELSE CAST(COUNT(l.id_login) AS VARCHAR(10))

END AS ORDER_BY

FROM dbo.DEMO_USUARIOS u

LEFT OUTER JOIN dbo.DEMO_LOGINS l -- OUTER JOIN ya que puede haber usuarios

on l.id_usuario = u.id_usuario -- que nunca se han logeado en el sistema

GROUP BY u.id_usuario,

u.usuario,

u.departamento

ORDER BY ORDER_BY

 

END

GO

 

Ya vamos progresando, ejecutamos el SP y ejecuta, vamos a probarlo

 

 

EXEC spLoginStats 0 -- ordenar por nombre de usuario

GO

 

id_usuario usuario departamento total_de_logins ORDER_BY

----------- -------------------- ------------ --------------- --------------------

3 admin IT 3 admin

5 alguien COBROS 0 alguien

4 supervisor VENTAS 6 supervisor

1 toravalenzuela IT 10 toravalenzuela

2 vendedor VENTAS 6 vendedor

6 YoNuncaEntro DIRECCION 0 YoNuncaEntro

 

 

 

 

EXEC spLoginStats 1 -- ordenar por nombre de departamento

GO

 

id_usuario usuario departamento total_de_logins ORDER_BY

----------- -------------------- ------------ --------------- --------------------

5 alguien COBROS 0 COBROS

6 YoNuncaEntro DIRECCION 0 DIRECCION

1 toravalenzuela IT 10 IT

3 admin IT 3 IT

4 supervisor VENTAS 6 VENTAS

2 vendedor VENTAS 6 VENTAS

 

 

 

 

EXEC spLoginStats 2 -- ordenar por total de inicios de sesión de mayor a menor

GO

 

id_usuario usuario departamento total_de_logins ORDER_BY

----------- -------------------- ------------ --------------- --------------------

5 alguien COBROS 0 0

6 YoNuncaEntro DIRECCION 0 0

1 toravalenzuela IT 10 10

3 admin IT 3 3

4 supervisor VENTAS 6 6

2 vendedor VENTAS 6 6

 

 

 

 

 

Ok, ya funciona, excepto de que, al pasarle @ORDER_BY = 2 (ordenar por total de inicios de sesión de mayor a menor) nos ordena de mayor a menor pero considerando que es un varchar:

“10“ > “3“

“3“ > “6“

“0“ > “99....99“

 

Vamos a modificar una vez mas nuestro CASE para que el varchar que tiene que ordenar sea con el siguiente formato ######, en otras palabras si un usuario ha entrado 6 veces la columna será algo como varchar “000006”, aunque antes de convertirlo en varchar vamos a realizar una resta, vamos a restar 999999 – (el numero de inicios de sesión) ósea si a entrado 6 veces será “999993”.

 

Esta operación medio mafufa se debe a que al ordenar varchar

“999990” (9 inicios de sesión) > es “mayor” que “999993” (6 inicios de sesión).

 

El SP quedaría:

 

 

 

/******************************************************************************

AUTOR ToraValenzuela

DESC: Obtenen las estadisticas de cuantas veces

se ha logeado un usuario en la app

@ORDER_BY 0 ordenar por nombre de usuario

1 ordenar por nombre de departamento

2 ordenar por total de inicios de sesión de mayor a menor

MODIFICACIONES - Realizamos un REPLICATE con Z para ordenar mayor a menor en @ORDER_BY = 2

- Realiza CAST a VARCHAR en el CASE

******************************************************************************/

ALTER PROCEDURE dbo.spLoginStats

@ORDER_BY int = 0

AS

BEGIN

 

SELECT u.id_usuario,

u.usuario,

u.departamento,

COUNT(l.id_login) AS total_de_logins, -- Total de logins en el sistema por cada usuario

-- Creamos una columna que nos va a servir de apoyo para ordenar

CASE (@ORDER_BY)

WHEN 0 THEN u.usuario

WHEN 1 THEN u.departamento

ELSE --CAST(COUNT(l.id_login) AS VARCHAR(10))

REPLICATE('0', 6-len(CONVERT(varchar(6),

(999999-COUNT (l.id_login))

))) +

CAST( (999999-COUNT (l.id_login)) AS Varchar(6))

END AS ORDER_BY

FROM dbo.DEMO_USUARIOS u

LEFT OUTER JOIN dbo.DEMO_LOGINS l -- OUTER JOIN ya que puede haber usuarios

on l.id_usuario = u.id_usuario -- que nunca se han logeado en el sistema

GROUP BY u.id_usuario,

u.usuario,

u.departamento

ORDER BY ORDER_BY

 

END

GO

 

Vamos a probar de nuevo @ORDER_BY = 2

 

EXEC spLoginStats 2 -- ordenar por total de inicios de sesión de mayor a menor

GO

 

id_usuario usuario departamento total_de_logins ORDER_BY

----------- -------------------- ------------ --------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 toravalenzuela IT 10 999989

2 vendedor VENTAS 6 999993

4 supervisor VENTAS 6 999993

3 admin IT 3 999996

5 alguien COBROS 0 999999

6 YoNuncaEntro DIRECCION 0 999999

 

 

 

 

Por fin funciona nuestro Stored Procedure!!!!!

 

Les dejo adjuntos el archivo con el SQL de este pequeño desmadre.

 

Diagrama de las tablas usadas (JPG)

SQL con el esquema de las tablas

SQL con la creacion y modificaciones al SP

Si ustedes encuentran una mejor forma de hacerlo por favor compartan y pónganlo en los comentarios.

 

Later

 

Tora