Vistas y Procedimientos almacenados ¿?¿?

[jcl]

Hola a todos,

Trato de hacer una vista con el asistente de SQLServer, pero me parece que nos es como los SP, que permiten pasar parámetros ... ¿Es así o estaré equivocado? Por favor, ¿Habrá alguien que me saque de dudas?.

Gracias.

[Mithrandir]

Efectivamente, las vistas no permiten el paso de parámetros, son para filtros previamente definidos.

Si el SP no te sirve, y la vista no hace lo que quieres te recomiendo las UDF (User Defined Functions), que son una mezcla de ambos (aunque mas lentas en ejecucion) y seguramente te serán de utilidad.

[jcl]

Gracias. Veo que "Albert Einstein" tenía razón. Además de tu recomendación, yo en tu lugar, agregaría un ejemplo para que tu interlocutor, culquiera que fuera, tenga mejor idea sobre el asunto que tratas.

¿Puedes darme un ejemplo de esas UDF o indicarme dónde leer al respecto?

Gracias, nuevamente ... y cuidate de cualquier "prejuicio".

[Mithrandir]

Tengo la (mala) costumbre de pegar pedazos de la ayuda de SQL, si tienes alguna duda en particular, no dudes en preguntar

SQL User-Defined Functions
Functions in programming languages are subroutines used to encapsulate frequently performed logic. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all of the function logic.

Microsoft® SQL Server™ 2000 supports two types of functions:

Built-in functions
Operate as defined in the Transact-SQL Reference and cannot be modified. The functions can be referenced only in Transact-SQL statements using the syntax defined in the Transact-SQL Reference. For more information about these built-in functions, see Using Functions.

User-defined functions
Allow you to define your own Transact-SQL functions using the CREATE FUNCTION statement. For more information about these built-in functions, see User-defined Functions.

User-defined functions take zero or more input parameters, and return a single value. Some user-defined functions return a single, scalar data value, such as an int, char, or decimal value.

For example, this statement creates a simple function that returns a decimal:

CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters.
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END

This function can then be used anywhere an integer expression is allowed, such as in a computed column for a table:

CREATE TABLE Bricks
(
BrickPartNmbr int PRIMARY KEY,
BrickColor nchar(20),
BrickHeight decimal(4,1),
BrickLength decimal(4,1),
BrickWidth decimal(4,1),
BrickVolume AS
(
dbo.CubicVolume(BrickHeight,
BrickLength, BrickWidth)
)
)

SQL Server 2000 also supports user-defined functions that return a table data type:

A function can declare an internal table variable, insert rows into the variable, and then return the variable as its return value.


A class of user-defined functions known as in-line functions, return the result set of a SELECT statement as a variable of type table.
These functions can be used in places where table expressions can be specified. For more information about the table data type, see Using Special Data.

User-defined functions that return a table can be powerful alternatives to views. A user-defined function that returns a table can be used where table or view expressions are allowed in Transact-SQL queries. Views are limited to a single SELECT statement; however, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.

A user-defined function that returns a table can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, whereas stored procedures that return result sets cannot. For example, fn_EmployeesInDept is a user-defined function that returns a table and can be invoked by a SELECT statement:

SELECT *
FROM tb_Employees AS E,
dbo.fn_EmployeesInDept('shipping') AS EID
WHERE E.EmployeeID = EID.EmployeeID

This is an example of a statement that creates a function in the Northwind database that will return a table:

CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S
INNER JOIN Orders AS O ON (S.ShipperID = O.ShipVia)
WHERE O.Freight > @FreightParm
RETURN
END

In this function, the local return variable name is @OrderShipperTab. Statements in the function build the table result returned by the function by inserting rows into the variable @OrderShipperTab. External statements invoke the function to reference the table returned by the function:

SELECT *
FROM LargeOrderShippers( $500 )

Revisa que la manera de llamarlos es igual que una tabla, pero pasandole parametros SELECT * FROM funcion(param1, param2, ...)