Stored Procedures y Functions en SQL Server 2005 – II

Functions en SQL Server 2005

 

Al igual que las funciones en los lenguajes de programación, las funciones definidas por el usuario de Microsoft SQL Server 2005 son rutinas que aceptan parámetros, realizan una acción, como un cálculo complejo, y devuelven el resultado de esa acción como un valor. El valor devuelto puede ser un valor escalar único o un conjunto de resultados.

Ventajas de las funciones definidas por el usuario

Permiten una ejecución más rápida.
Al igual que los stored procedures, las funciones definidas por el usuario Transact-SQL reducen el costo de compilación del código Transact-SQL almacenando los planes en la caché y reutilizándolos para ejecuciones repetidas 
  Esto significa que no es necesario volver a analizar y optimizar la función definida por el usuario con cada uso, lo que permite obtener tiempos de ejecución mucho más rápidos.
Las funciones CLR ofrecen una ventaja de rendimiento importante sobre las funciones Transact-SQL para tareas de cálculo, manipulación de cadenas y lógica empresarial
. Las funciones Transact-SQL se adecuan mejor a la lógica intensiva del acceso a datos.

Pueden reducir el tráfico de red.
Una operación que filtra datos basándose en restricciones complejas que no se puede expresar en una sola expresión escalar se puede expresar como una función. La función se puede invocar en la cláusula WHERE para reducir el número de filas que se envían al cliente.

Tipos de funciones

·  Funciones escalares

Las funciones escalares definidas por el usuario devuelven un único valor de datos del tipo definido en la cláusula RETURNS. Las funciones escalares en línea no tienen cuerpo; el valor escalar es el resultado de una sola instrucción. Para una función escalar de múltiples instrucciones, el cuerpo de la función, definido en un bloque BEGIN…END, contiene una serie de instrucciones Transact-SQL que devuelven el valor único

Demo:

IF OBJECT_ID (N‘dbo.ufnGetInventoryStock’, N‘FN’) IS NOT NULL
     
DROP FUNCTION ufnGetInventoryStock;
GO

CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
   RETURNS int
AS
— Returns the stock level for the product.
BEGIN

DECLARE @ret int;
SELECT @ret = SUM(p.Quantity)  FROM Production.ProductInventory p
WHERE p.ProductID = @ProductID AND p.LocationID = ‘6’;

  IF (@ret IS NULL)
   
SET @ret = 0
   
RETURN @ret
END;
GO

— Utilizando la nueva function
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;

·         Funciones con valores de tabla

Las funciones con valores de tabla definidas por el usuario devuelven un tipo de datos table. Las funciones con valores de tabla en línea no tienen cuerpo; la tabla es el conjunto de resultados de una sola instrucción SELECT

Demo: Creando una Funcion con valores de tabla en linea

IF OBJECT_ID (N‘Sales.ufn_SalesByStore’, N‘IF’) IS NOT NULL
    
DROP FUNCTION Sales.ufn_SalesByStore;
GO

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
  RETURNS TABLE
AS

RETURN SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS ‘YTD Total’ FROM Production.Product AS P
  
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
 
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
 
WHERE SH.CustomerID = @storeid
 
GROUP BY P.ProductID, P.Name);
GO

— Utilizando la ufn_SalesByStore function
SELECT * FROM Sales.ufn_SalesByStore (602);

Para una función con valores de tabla de múltiples instrucciones, el cuerpo de la función, definido en un bloque BEGIN…END, contiene una serie de instrucciones Transact-SQL que generan e insertan filas en la tabla que se va a devolver.

Demo: Creando una Funcion con valores de tabla de multiples instrucciones:


CREATE
FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE (
     EmployeeID
int primary key NOT NULL,
    
Name nvarchar(255) NOT NULL,
     Title
nvarchar(50) NOT NULL,
     EmployeeLevel
int NOT NULL,
     Sort
nvarchar (255) NOT NULL
)

–Returns a result set that lists all the employees who report to the
–specific employee directly or indirectly.*/
AS
BEGIN
   WITH
DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
      
(SELECT CONVERT(Varchar(255), c.FirstName + ‘ ‘ + c.LastName), e.Title, e.EmployeeID,1,
      
CONVERT(Varchar(255), c.FirstName + ‘ ‘ + c.LastName)
      
FROM HumanResources.Employee AS e
          JOIN Person.Contact
AS c ON e.ContactID = c.ContactID
     
WHERE e.EmployeeID = @InEmpID
   
UNION ALL
     SELECT
CONVERT(Varchar(255), REPLICATE (‘| ‘ , EmployeeLevel) + c.FirstName + ‘ ‘ + c.LastName), e.Title, 
      EmployeeID, EmployeeLevel + 1,
CONVERT (Varchar(255), RTRIM(Sort) + ‘| ‘ + FirstName + ‘ ‘ + LastName)
    
FROM HumanResources.Employee as e JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports
AS d ON e.ManagerID = d.EmployeeID )

— copy the required columns to the result of the function
  INSERT @retFindReports SELECT EmployeeID, Name, Title, EmployeeLevel, Sort FROM DirectReports
  RETURN
END
;
GO

— Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel FROM dbo.ufn_FindReports(109) ORDER BY Sort;

·         Funciones integradas

SQL Server proporciona las funciones integradas para ayudarle a realizar diversas operaciones. No se pueden modificar. Puede utilizar funciones integradas en instrucciones Transact-SQL para:
            
        
ü
  Tener acceso a información de las tablas del sistema de SQL Server sin tener acceso a las tablas del sistema directamente.
        
ü
  Realizar tareas habituales como SUM, GETDATE o IDENTITY.

Las funciones definidas por el usuario se han ampliado en Microsoft SQL Server 2005 para incluir las funciones escritas en cualquier lenguaje de programación que se admita en Microsoft .NET Framework

Remember:

 

Las funciones en línea pueden utilizarse para obtener la funcionalidad de vistas con parámetros. No obstante, las vistas no admiten parámetros en las condiciones de búsqueda especificadas en la cláusula WHERE. Las funciones en línea definidas por el usuario pueden utilizarse para admitir parámetros en las condiciones de búsqueda especificadas en la cláusula WHERE.

No se recomienda el uso de funciones definidas por el usuario que producen efectos secundarios en la base de datos.

Cuando se llama desde una función, el procedimiento almacenado extendido no puede devolver conjuntos de resultados al cliente

En general, si el procedimiento almacenado devuelve un solo conjunto de resultados, defina una función con valores de tabla.

Para las funciones con valores de tabla no son necesarias las instrucciones EXECUTE dinámicas

Las funciones CLR suponen una alternativa más confiable y escalable frente a los procedimientos almacenados extendidos. Muchos procedimientos almacenados extendidos realizan tareas de cálculo que son difíciles de expresar en Transact-SQL Además, los procedimientos almacenados extendidos que devuelven conjuntos de resultados mediante el acceso a un recurso externo, como un archivo o un servicio Web, se pueden volver a escribir mediante una función con valores de tabla CLR.

About justindeveloper

I am MCP (Microsoft Certified Professional). MCTS (Microsoft Certified Technology Specialist) and MCPD (Microsoft Certified Professional Developer), also I am SAP Business One Certified!! Desarrollando desde el IDE de Visual Studio NET 2003 hasta ahora con el Visual Studio NET 2010. Desde Microsoft SQL Server 2000 hasta ahora con el Microsoft SQL Server 2008 R2 y tambien con SharePoint, desde WSS 3.0 y MOSS 2007 y ahora familirizandome con el Sharepoint Foundation 2010 & Sharepoint Server 2010. The software development will follow being every time more wonderful!
This entry was posted in Development. Bookmark the permalink.

3 Responses to Stored Procedures y Functions en SQL Server 2005 – II

  1. Carlos Rojas says:

    Hola:

    he reado una funcion que me devuelve la zona horaria de un pais pero quiero que esta funcion se execute cuando realizo un Insert dentro de una tabla, como puedo hacer para que esta funcion me quede como valor por defecto al realizar un insert dentro de la tabla.

    espero se entienda y me ayudes
    Gracias

    • Hola, Disculpa por la demora, realmente por el trabajo no leo mucho los comentarios que se pueda realizar en los post.

      A tu consulta ( espero no sea demasiado tarde). Lo que haría es crear un Stored Procedure que se encargue de realizar la inserción en la tabla que deseas y dentro de este SP llamar a la función que te devuelve la zona horaria utilizando en el insert.

      • Hola disculpa la demora. A tu consulta, puedes otorgar los siguientes permisos:

        View Definition

        y hasta le puedes dar permisos de crear una colección de schema XML.

        Create XML schema collection

        Estos permisos son configurados en las properties a nivel de Base de datos.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s