Vistas y Procedimientos almacenados ¿?¿?

[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, ...)