Cursor con parámetros en procedimiento almacenado

[estibbons]

Hola

Tengo que hacer un procedimiento almacenado un poco complejo y creo que la única solución es la de usar un cursor.

El modo de funcionamiento del procedimiento es:

- primero se define una lista de registros con los que debe trabajar, usando un cursor
- se recorre ese cursor y con cada registro obtiene más datos usando varias consultas, llamadas a otros procedimientos y a funciones
- una vez obtenidos todos los datos de cada registro, los graba en una tabla temporal
- por último hace una select de la tabla temporal uniéndola con otras tablas para completar los datos que devuelve.
- bueno, antes de finalizar cierra el cursor y libera memoria.

Como el proceso es muy lento, quiero poder filtrar con qué registros voy a trabajar en el momento de abrir el cursor, no al recorrerlo. Algo así como:

SET @miCursor = CURSOR FOR SELECT Id FROM Tabla WHERE @Where

Resumiendo, ¿hay alguna forma de poder especificar la cláusula where del cursor de forma dinámica?

Graaaacias.

[iislas]

Olvidate de los cursores, sin conocer mas de lo que nos has comentado, en lugar de crear un cursor, ¿porque no trabajas con tablas temporales?

[estibbons]

Iislas, puedo grabar los datos en tablas temporales, de hecho ya utilizo una, pero necesito leer los registros de uno en uno para poder hacer los cálculos de cada registro y que yo sepa la única forma de hacerlo dentro de un procedimiento almacenado es usando cursores, ¿no?

Algunos de esos cálculos a hacer con los registros son complejos, hechos por procedimientos y/o funciones, y no tengo muy claro que puedan sustituirse por consultas sql.

Lo que se me ocurre sería realizar un proceso de este tipo:

- crear una tabla temporal (interna al procedimiento almacenado)
- llenar la tabla temporal con los registros con los que voy a trabajar
- hacer updates de los registros de la tabla temporal con consultas de este tipo

update #miTablaTemp
set campo1 = miFuncion1(Id)

update #miTablaTemp
set campo2 = miFuncion2(Id, campo1), campo3 = miFuncion3(Id, campo1)

update #miTablaTemp
set campo4 = miFuncion4(Id, campo2)

siendo miFuncionX funciones que devuelven valores escalares.

- y hacer el select final de esa tabla temporal

[iislas]

"la única forma de hacerlo dentro de un procedimiento almacenado es usando cursores, ¿no?"

R= No

Puedes hacerlo con tablas temporales tambien:

CREATE TABLE ##mytemporal (id int identity(1,1), nombre varchar(50), sueldo smallmoney)
INSERT INTO ##mytemporal (nombre, sueldo)
SELECT nombre, sueldo FROM EMPLEADO WHERE Depto = 1156
DECLARE @Registro INT
SET ROWCOUNT 1
WHILE EXISTS(SELECT id FROM ##mytemporal)
BEGIN
SELECT @Registro = id FROM ##mytemporal
--------------------------------------------------------------------
--------------------------------------------------------------------
--- CODIGO SQL (CALCULOS, ETC.)
--------------------------------------------------------------------
--------------------------------------------------------------------
DELETE ##mytemporal WHERE id = @Registro
CONTINUE
END

[estibbons]

Pues he vuelto a hacer el procedimiento, pero siguiendo el método que comentas, y así es más lento.

Usando un cursor tarda 8 segundos, mientras que con el método que comentas, en el que tengo que usar dos tablas temporales, me tarda 9 segundos.


Gracias por las respuestas, iislas :)

[iislas]

¿creaste indices?, si no son muchos los datos a ingresar o procesar, maneja objetos tipo table

[estibbons]

Sí, tengo índices en los sitios en los que puedo usarlos (una parte de la aplicación está en una base de datos que no puedo tocar, sólo acceder con consultas).

Al final he usado el método que me has recomendado de usar una tabla temporal en vez de un cursor, así puedo crear la cláusula where según los parámetros que me pasen para la selección inicial, lo que acelera mucho el proceso.

[chuchufuentes]

Yo tengo el mismo problemilla, pero un poco mas complicado por que mi la tabla a leer tiene 124 campos con un aoriximado de 20000 registros tiene index, modificarla no puedo, Por favor dime como resolviste, te agradezco
Gracias

[estibbons]

Al final el procedimiento sigue estos pasos:

1- declaro una tabla temporal para guardar las referencias de los registros con los que voy a trabajar

2- declaro una tabla temporal para guardar los datos finales, con los cálculos ya hechos

3- genero la consulta SQL para obtener los registros con los que voy a trabajar. Esta consulta hace un INSERT INTO la primera tabla temporal.

4- recorro la tabla temporal leyendo los registros. por cada registro:

4.a) obtengo los datos asociados al registro

4.b) hago los cálculos necesarios

4.c) hago un INSERT INTO de los datos calculados en la segunda tabla temporal

4.d) borro el registro que procesé para seguir con el bucle con el siguiente

5- a continuación, hago un SELECT de la segunda tabla temporal para devolver la consulta resultante

6- por último, borro las dos tablas temporales que usé en el proceso

[estibbons]

Quizás lo veas mejor con código, el método que utilicé es más o menos este:

CREATE PROCEDURE [dbo].[Prueba]
@Texto1 VARCHAR(20),
@Texto2 VARCHAR(20),
@Texto3 VARCHAR(20)
AS
BEGIN
DECLARE @sSQL NVARCHAR(2048)
DECLARE @sWhere NVARCHAR(1024)

DECLARE @NIF NVARCHAR(14)
DECLARE @Nombre NVARCHAR(40)
DECLARE @Cantidad1 INT
DECLARE @Cantidad2 INT
DECLARE @Cantidad3 INT

-- Tabla temporal que contendrá los registros que vamos a procesar
CREATE TABLE #TablaTmp1 (
ClaveTT1 INT IDENTITY(1,1),
NIF NVARCHAR(14),
Nombre NVARCHAR(40),
)

-- Tabla temporal para guardar los datos calculados
CREATE TABLE #TablaTmp2 (
NIF NVARCHAR(14),
Nombre NVARCHAR(40),
Cantidad1 INT,
Cantidad2 INT,
Cantidad3 INT
)

-- Se calcula la cláusula Where según los parámetros pasados al procedimiento
SET @sWhere = ''

IF @Texto1 <> ''
IF @sWhere <> ''
SET @sWhere = @sWhere + ' AND '
SET @sWhere = '(Texto1 LIKE ''%' + @Texto1 + ''')'

IF @Texto2 <> ''
IF @sWhere <> ''
SET @sWhere = @sWhere + ' AND '
SET @sWhere = '(Texto2 LIKE ''%' + @Texto2 + ''')'

IF @Texto3 <> ''
IF @sWhere <> ''
SET @sWhere = @sWhere + ' AND '
SET @sWhere = '(Texto3 LIKE ''%' + @Texto3 + ''')'

-- Se calcula la cláusula INSERT INTO
SET @sSQL = 'INSERT INTO #TablaTmp1'
SET @sSQL = @sSQL + ' (NIF, Nombre)'
SET @sSQL = @sSQL + ' SELECT NIF, Nombre'
SET @sSQL = @sSQL + ' FROM Personas'
IF @sWhere <> ''
SET @sSQL = @sSQL + ' WHERE ' + @sWhere

EXEC (@sSQL)

-- Bucle que lee los registros de la tabla temporal 1 para hacer los
-- cálculos oportunos y guardar los datos obtenidos en la tabla
-- temporal2
WHILE EXISTS (SELECT ClaveTT1 FROM #TablaTmp1)
BEGIN

SELECT TOP 1
@ClaveTT1 = ClaveTT1,
@NIF = NIF,
@Nombre = Nombre
FROM #TablaTmp1

-- Llamadas a los procedimientos y funciones que realizan los
-- cálculos complejos:

SET @Cantidad1 = FuncionCalculo1(@NIF)
SET @Cantidad2 = FuncionCalculo2(@NIF)
SET @Cantidad3 = FuncionCalculo3(@NIF)


INSERT INTO #TablaTmp2
(NIF, Nombre, Cantidad1, Cantidad2, Cantidad3)
VALUES (
@NIF,
@Nombre,
@Cantidad1,
@Cantidad2,
@Cantidad3
)

DELETE #TablaTmp1
WHERE ClaveTT1 = @ClaveTT1

END

SELECT NIF,
Nombre,
Cantidad1,
Cantidad2,
Cantidad3
FROM #TablaTmp2
ORDER BY NIF

DROP TABLE #TablaTmp2
DROP TABLE #TablaTmp1

RETURN @@ROWCOUNT

END

[Andres95]

Una alternativa al codigo anterior...

Código:

Select NIF
      ,Nombre
      ,FuncionCalculo1(@NIF) Cantidad1
      ,FuncionCalculo2(@NIF) Cantidad2
      ,FuncionCalculo3(@NIF) Cantidad3
FROM  Personas
Where Case When isnull(@Texto1,'') = '' 
           Then 1 
           Else Case When Texto1 = @Texto1 Then 1 Else 0 End   
           End = 1                       And
Case When isnull(@Texto2,'') = '' 
           Then 1 
           Else Case When Texto2 = @Texto2 Then 1 Else 0 End   
           End = 1
Case When isnull(@Texto3,'') = ''        And
           Then 1 
           Else Case When Texto3 = @Texto3 Then 1 Else 0 End   
           End = 1

Saludos!

[chuchufuentes]

Iniciado por Andres95

Una alternativa al codigo anterior...

Código:

Select NIF
      ,Nombre
      ,FuncionCalculo1(@NIF) Cantidad1
      ,FuncionCalculo2(@NIF) Cantidad2
      ,FuncionCalculo3(@NIF) Cantidad3
FROM  Personas
Where Case When isnull(@Texto1,'') = '' 
           Then 1 
           Else Case When Texto1 = @Texto1 Then 1 Else 0 End   
           End = 1                       And
Case When isnull(@Texto2,'') = '' 
           Then 1 
           Else Case When Texto2 = @Texto2 Then 1 Else 0 End   
           End = 1
Case When isnull(@Texto3,'') = ''        And
           Then 1 
           Else Case When Texto3 = @Texto3 Then 1 Else 0 End   
           End = 1

Saludos!