Procedure o View en Report..

29/07/2003 - 00:05 por Manolo Zepeda | Informe spam
hola amigos..!!

Que es mas recomendable de usar en los
reportes..Procedimientos o Vistas??Porque?

Saludos..
 

Leer las respuestas

#1 Carlos Luis Rojas Vargas
30/07/2003 - 07:10 | Informe spam
Hola:

En mi caso he obtenido excelentes resultados utilizando Funciones
Definidas por el usuario del tipo "Multi-statement table-valued function",
ya que me permiten retornar un objeto tipo "Table"(nuevo en SQL-Server
2000), personalizando los campos, las llaves y el contenido del resultado,
con un muy buen rendimiento.

Te adjunto el ejemplo del Books Online:

C. Multi-statement table-valued function
Given a table that represents a hierarchical relationship:

CREATE TABLE employees (empid nchar(5) PRIMARY KEY,
empname nvarchar(50),
mgrid nchar(5) REFERENCES employees(empid),
title nvarchar(30)
)

The table-valued function fn_FindReports(InEmpID), which -- given an
Employee ID -- returns a table corresponding to all the employees that
report to the given employee directly or indirectly. This logic is not
expressible in a single query and is a good candidate for implementing as a
user-defined function.

CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
DECLARE @RowsAdded int
DECLARE @reports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30),
processed tinyint default 0)
INSERT @reports
SELECT empid, empname, mgrid, title, 0
FROM employees
WHERE empid = @InEmpId
SET @RowsAdded = @@rowcount
WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @reports
SET processed = 1
WHERE processed = 0
INSERT @reports
SELECT e.empid, e.empname, e.mgrid, e.title, 0
FROM employees e, @reports r
WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
SET @RowsAdded = @@rowcount
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @reports
SET processed = 2
WHERE processed = 1
END

INSERT @retFindReports
SELECT empid, empname, mgrid, title
FROM @reports
RETURN
END
GO

SELECT *
FROM fn_FindReports('11234')
GO



Saludos,


Carlos Rojas Vargas
Costa Rica


"Salvador Ramos" wrote in message
news:eG$
Mostrar la cita
utilizar
Mostrar la cita
óptima,
Mostrar la cita

Preguntas similares