Varios registros en un store procedure

[yera2002]

Hola a todos:

Tengo el siguiente problema, quiero hacer una consulta a una tabla que tiene mucha informacion, pero en varias ocasiones me ha indicado el error "QUERY TIME OUT", la tabla donde tengo la informacion esta bien indexada, pero este reporte lo ejecuto desde una aplicacion cliente.

Ahora mi duda es alguien podria asesorame como crear un Store Procedure para poder ejecutar esta sentencia SQL, ya lo intente pero a decir verdad no consigo informacion del mismo. Les muestro lo que quiero y lo que llevo del Store Procedure.

Mi Tabla

Usuario FechaAcceso Operación Tiempoacceso
USUARIO 1 30/01/2010 REVISION 1 HR
USUARIO 2 30/01/2010 REVISION 2 HR
USUARIO 3 30/01/2010 CORTE 3 MIN
USUARIO 1 30/01/2010 CORTE 3 MIN
USUARIO 2 10/02/2010 ALTA 2 MIN
USUARIO 3 10/02/2010 ALTA 2 MIN
USUARIO 1 09/02/2010 ALTA 2 MIN
USUARIO 2 09/02/2010 BAJA 2 MIN
USUARIO 3 09/02/2010 BAJA 2 MIN
USUARIO 4 09/02/2010 ALTA 2 MIN
Vamos a suponer que requiero obtener el usuario, la fecha de acceso, la operacion y el tiempo de acceso

SELECT usuario, fechaacceso, operacion, tiempoacceso FROM usuarios

Si quisiera filtrar la informacion por varios campos, para que solo muestre la informacion del usuario1 o usuario 2, que hayan dado de alta o revision entre el 30 de enero de 2010 y 10 de febrero del 2010

SELECT usuario, fechaacceso, operacion, tiempoacceso FROM usuarios
WHERE usuario >= 'USUARIO 1' AND usuario <= 'USUARIO 2' AND (operacion = 'REVISION' OR operacion = 'ALTA') AND fechaacceso >= '20100101' AND fechaacceso <= '20100210'


lo que tengo del store procedure
ALTER PROCEDURE [dbo].[Usuariosxoperacionxfecha]
--Las variable de entrada
@UsuarioInicial nvarchar(13) = '',
@UsuarioFinal nvarchar(13) = '',
@OperacionInicial nvarchar(20) = '',
@OperacionFinal nvarchar(20) = '',
@dfechainicial datetime = '01-01-2000',
@dfechaFinal datetime = '01-01-3000',
@usuario nvarchar(13) = '' OUTPUT,
@operacion nvarchar(100) = '' OUTPUT,
@tiempoacceso nvarchar(8 = '' OUTPUT,
@fechaacceso datetime = '01-01-2000' OUTPUT,
AS

BEGIN

SELECT @usuario = usuario,@fechaacceso = fechaacceso, @operacion = operacion, @tiempoacceso = tiempoacceso FROM usuarios
WHERE usuario >= @UsuarioInicial AND usuario <= @UsuarioFinal AND (operacion = @OperacionInicial OR operacion = @OperacionFinal) AND fechaacceso >= @dfechainicial AND fechaacceso <= @dfechaFinal

-- El resultado del Store Procedure
SELECT @usuario,@fechaacceso, @operacion, @tiempoacceso
END

Ojala alguien me pueda guiar, no souy muy experta en Store Procedures

Saludos...

[flaviovich]

Así has probado?

Código SQL:

ALTER PROCEDURE [dbo].[Usuariosxoperacionxfecha]
--Las variable de entrada
@UsuarioInicial nvarchar(13) = '',
@UsuarioFinal nvarchar(13) = '',
@OperacionInicial nvarchar(20) = '',
@OperacionFinal nvarchar(20) = '',
@dfechainicial datetime = '01-01-2000',
@dfechaFinal datetime = '01-01-3000'
AS
 
BEGIN
 
SELECT usuario, fechaacceso, operacion, tiempoacceso
FROM usuarios
WHERE usuario >= @UsuarioInicial AND usuario <= @UsuarioFinal
AND (operacion = @OperacionInicial OR operacion = @OperacionFinal)
AND fechaacceso >= @dfechainicial AND fechaacceso <= @dfechaFinal
 
END
 

[yera2002]

Hola flaviovich:

Ya proe como me sugeriste, el detalle es que no me envia ninguna informacion

Saludos...

[flaviovich]

Cómo estas llamando al procedimiento?
Es decir, qué parámetros le estás pasando?

[yera2002]

Mi forma de llamarlo es:

EXEC [dbo].[VtasXCliXFac] @UsuarioInicial = 'USUARIO1',
@UsuarioFinal = 'USUARIO2',
@OperacionInicial = 'REVISION',
@OperacionFinal = 'ALTA',
@dfechainicial datetime = '01-01-2010',
@dfechaFinal datetime = '10-02-2010'

[yera2002]

Ya vi mi error Si me esta rgresando valores correctos, estaba enviando mal los valores de entrada.

Gracias flaviovich

Un Saludo

[yera2002]

Tengo una nueva duda, ojala me puedas ayudar tal cual como lo hiciste anteriormente.

¿Podria condicionar el WHERE?

Es decir, si quiero generar la sentencia condicionada unicamente con el filtro de operacion y fecha, de tal modo que si yo elijo enviar el usuario me agrege al WHERE la parte de "usuario >= @UsuarioInicial AND usuario <= @UsuarioFinal" y si no lo envio no lo ponga, del mismo modo para los otros filtros, no se quiza generar una cadena que se agrege a la sentenci SQL

¿Es vible?
¿Se puede?
¿Como seria?

[iislas]

Con el permiso de Flaviovich

Puedes usarlo, solo que seria con LIKE

usuario LIKE ISNULL(@UsuarioFinal+'%', '%')

[yera2002]

Gracias Por responder iislas:

Creono me explique muy bien, no tengo problema con usar el Like, aqui veo 2 cosas, con el ejemplo que pus se puede presentar el caso de tener un usuario "USUARIO 20", "USUARIO 21", ETC Si utilizo un like, me mostrará tambien la informacion del esos usuarios, además del usuario "USUARIO 2". Ahora mi duda básica es la siguiente. Trataré de ser un poco más explicita.

En visual Basic script, yo corro un proceso donde le solicito al usuario los filtros para un reporte. Estos filtros son:
1. Usuario
1.1 Usuario Inicial
1.2 Usuario Final
2. Operacion
2.1. Operacion 1
2.2. Operacion 2
3. FechaAcceso
3.1. Fecha Acceso Inicial
3.2. Fecha Acceso Final

Con VB Script yo puedo concatenar una cadena y decir que voy a enviar ago asi como:

If Not IsNull(UsuarioInicial) AND Not IsNull(UsuarioFinal) Then
cCondicion = " AND usuario >= '" & Trim(usuarioInicial) & "'AND usuario <= '" & Trim(usuarioFinal) & "'"
End If

If Not IsNull(Operacion 1) Then
If Not IsNull(Operacion 2) Then
cCondicion = cCondicion " AND (operacion = '" & Trim(Operacion1) & "' OR operacion = '" &Trim(Operacion2) & "')"
Else
cCondicion = cCondicion " AND operacion = '" & Trim(Operacion1) & "'"
End If
ElseIf Not IsNull(Operacion 2) Then
cCondicion = cCondicion " AND operacion = '" & Trim(Operacion1) & "'"
End If

If Not IsNull(FechaInicial) AND Not IsNull(FechaFinal) Then
cCondicion = " AND fechaacceso >= " & FechaSerial(fechaInicial) & " AND fechaacceso<= " & FechaSerial(fechaFinal)
End If

De tal modo que al crear el rcordset (anteriormente al store Procedure) pudiera yo determinar que condicionante mando.

Y dijera algo asi:

strSQL = ""
strSQL = strSQL & "SELECT usuario, fechaacceso, operacion, tiempoacceso "
strSQL = strSQL & "FROM usuarios "
strSQL = strSQL & "WHERE 1 = 1 "
strSQL = strSQL & cCondicion

La aplicacion lo hace bien, pero creo via Store Procedure sera más rapido y no se presentaria el error de Query Time Out. Quiero saber si est condicionante, la puedo evar via store procedure e implementar un query algo asi:

SELECT usuario, fechaacceso, operacion, tiempoaccesoFROM usuariosWHERE 1 = 1 @cCondicion

En caso de no poder, ¿que sugieren?, por que creo que tendre que agregar todas las condicionantes posibles como Sentencias independientes

Saludos...

[iislas]

Tu problema ahora es de QUERY DINAMICO, ya que al manejar el codigo desde el front, este creara un plan de ejecucion totalmente diferente, incluso, dejando de utilizar indices.

Me gustaria ver mas comentario al respecto

[yera2002]

Ok iislas:

Buscare informacion al respecto y espero pronto poder dar mi solucion. De cualquier modo si alguien tiene algo que me puea ayudar lo agradezco.

Un saludo...

[yera2002]

Hola a todos:

Como creo que debe ser todo esto me dispongo a mostrarles las 2 solucines que encontre al problema que de inicio expuse. Forzar al cliente a darme siempre los datos que requiere el Query. De tal modo que mi Store Procedure quedo de la siguiente manera:

ALTER PROCEDURE [dbo].[Usuariosxoperacionxfecha]
--Las variable de entrada
@UsuarioInicial nvarchar(13) = '',
@UsuarioFinal nvarchar(13) = '',
@OperacionInicial nvarchar(20) = '',
@OperacionFinal nvarchar(20) = '',
@dfechainicial datetime = '01-01-2000',
@dfechaFinal datetime = '01-01-3000'
AS

BEGIN

SELECT usuario, fechaacceso, operacion, tiempoacceso
FROM usuarios
WHERE usuario >= @UsuarioInicial AND usuario <= @UsuarioFinal
AND (operacion = @OperacionInicial OR operacion = @OperacionFinal)
AND fechaacceso >= @dfechainicial AND fechaacceso <= @dfechaFinal

END

Esta opcion me la otorgo Flaviovich. En este procedimiento lo que realizo es enviar la informacion especifica de los filtros, esta misma se guarda en las variables @usuarioinicial, @usuariofinal,@operacioninicial,@operacionfinal,@d fechainicial y @fechafinal

Y la forma de ejecutarlo es:

Exec [dbo].[Usuariosxoperacionxfecha]
'USUARIO1','USUARIO30','ALTA','REVISION','20100101 ','20100228'

Por otro lado tambien probe con un query dinamico y el store procedure lo hice la siguiente manera

ALTER PROCEDURE [dbo].[Usuariosxoperacionxfecha]
--Las variable de entrada
@WHERE nvarchar(2000) = ''
AS

BEGIN
DECLARE @QUERY nvarchar(2000) = '';
SET @QUERY = ('SELECT usuario, fechaacceso, operacion, tiempoacceso
FROM usuarios' + @WHERE

EXEC (@QUERY)

END

En este procedimiento lo que hago es enviar todas las condicionantes dentro de la variable @WHERE y ejecuto el mismo antes de finalizar el procedimieto.

La forma de ejecutarlo es:

Exec [dbo].[Usuariosxoperacionxfecha]
' WHERE usuario >= ''USUARIO1'' AND usuario <= ''USUARIO2'' '

Debo hacer notar que las comillas que parecen dobles, son 2 comillas simples

Espero sirva de referencia.

Saludos...

[iislas]

Ya hemos comentado que se deje de utilizar el EXEC(QUERY) por EXEC sp_executesql query

[yera2002]

Hola IIslas:

Realizaré el cambio debido, gracias por la sugerencia

Saludos...