Stored Procedures y Functions en SQL Server 2005

Stored Procedures en SQL Server 2005

 

Todos sabemos que es un Stored Procedures; a modo de intro a este blog, un Stored Procedure es una colección guardada de instrucciones Transact-SQL o una referencia a un método Common Language Runtime (CLR) de Microsoft .NET Framework

Dicho procedimiento puede recopilar y devolver parámetros proporcionados por el usuario. Los procedimientos se pueden crear para uso permanente o para uso temporal en una sesión, un procedimiento local temporal, o para su uso temporal en todas las sesiones, un procedimiento temporal global.  Los procedimientos almacenados también se pueden crear de modo que se ejecuten de forma automática al iniciarse una instancia de SQL Server.

Tambien sabemos sobre los procedimientos temporales locales o globales, que se pueden crear anteponiendo el signo de número (#) al nombre de nuestro stored procedure (#procedure_name)  para los procedimientos temporales locales y dos signos de número para los procedimientos temporales globales (##procedure_name). Nota: No es posible especificar nombres temporales para los procedimientos almacenados CLR.

Un procedimiento almacenado puede tener un máximo de 2.100 parámetros.

En los procedimientos almacenados CLR, no es posible especificar como parámetros los tipos text, ntext, image, cursor y table. Si el tipo de datos del parámetro es un tipo definido por el usuario CLR, es necesario disponer de permiso EXECUTE en el tipo.

No es posible especificar RECOMPILE en los stored procedures CLR.
La opción ENCRYPTION
no es válida en los stored procedures  CLR.
No es posible especificar FOR REPLICATION en los stored procedures  CLR.
Un stored procedure FOR REPLICATION tendrá un tipo de objeto RF en sys.objects y sys.procedures
.
De manera predeterminada, SQL Server no puede ejecutar código CLR.  Es posible crear, modificar y quitar objetos de base de datos que hagan referencia a módulos CLR; no obstante, no es posible ejecutar esas referencias en SQL Server hasta que se haya habilitado la opción clr enabled. Para habilitar la opción, se utilizará: 
sp_configure ‘clr enable’,y luego:se ejecutará: Reconfigure.

El tamaño máximo de un stored procedure Transact-SQL es de 128 MB.

Utilizar parámetros con procedimientos almacenados CLR

Los parámetros de un procedimiento almacenado CLR pueden ser de cualquiera de los tipos de datos escalares del sistema de SQL Server.
Para que el Database Engine (Motor de base de datos) haga referencia al método correcto cuando está sobrecargado en .NET Framework, el método indicado en <method_specifier> debe tener las siguientes características:
        
ü
  Ser declarado un método estático.
        
ü
  Recibir el mismo número de parámetros que el número de parámetros del procedimiento.
        
ü
  No ser un constructor ni un destructor de su clase.
       
ü
 Utilizar tipos de parámetros compatibles con los tipos de datos de los parámetros correspondientes del procedimiento de SQL Server.
      
ü
  Devolver sus parámetros por referencia y no por valor siempre que se haya especificado OUTPUT para una declaración de parámetros específica.

Obtener información acerca de procedimientos almacenados

 

Para mostrar la definición de un procedimiento almacenado Transact-SQL, se debe utilizar la vista de catálogo sys.sql_modules de la base de datos en la que se encuentra el procedimiento.

Nota: El texto de un procedimiento almacenado creado con la opción ENCRYPTION no se puede ver mediante la vista de catálogo sys.sql_modules

Para obtener un informe sobre los objetos a los que hace referencia un procedimiento, se debe realizar una consulta a la vista de catálogo sys.sql_dependencies o utilizar sp_depends para devolver información sobre los objetos a los que hacen referencia los procedimientos almacenados CLR. Para mostrar información acerca de los procedimientos almacenados CLR, utilice la vista de catálogo sys.assembly_modules de la base de datos en la que se encuentra el procedimiento.

Para mostrar información acerca de los parámetros definidos en un procedimiento almacenado, se debe utilizar la vista de catálogo sys.parameters de la base de datos en la que se encuentra el procedimiento.

Un Remember: En el momento de la creación, sólo se realiza la comprobación de la sintaxis. El procedimiento almacenado no se compila hasta que se ejecuta por primera vez. Solamente durante la compilación se resuelven todos los objetos a los que se hace referencia en el procedimiento almacenado. Cuando un procedimiento se ejecuta por primera vez, se compila para determinar que dispone de un plan de acceso óptimo para recuperar los datos. En las siguientes ejecuciones del procedimiento almacenado se puede volver a utilizar el plan ya generado si aún permanece en la caché de planes del Database Engine

 

Parámetros que utilizan el tipo de datos cursor

 

Los procedimientos almacenados Transact-SQL sólo pueden utilizar el tipo de datos cursor para los parámetros OUTPUT. Si se especifica el tipo de datos cursor para un parámetro, se requieren los parámetros VARYING y OUTPUT.

Demo:Crear un procedimiento almacenado CLR


En el siguiente ejemplo se crea el procedimiento almacenado GetPhotoFromDB que hace referencia al método GetPhotoFromDB de la clase LargeObjectBinary del ensamblado HandlingLOBUsingCLR desde el Microsoft SQL Server Management Studio. Antes de que se cree el procedimiento almacenado, el ensamblado HandlingLOBUsingCLR se registra en la base de datos local.

 

CREATE ASSEMBLY HandlingLOBUsingCLR
Authorization [dbo] <
— si se quiere autorizar a un User Account especifico
From ‘\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll’
;
With Permission_Set = safe <
— el modo seguro es by default
GO

CREATE PROCEDURE dbo.GetPhotoFromDB
(
   @ProductPhotoID int
,
   @CurrentDirectory nvarchar(1024
),
   @FileName nvarchar(1024
)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB
;
GO

Demo2: Utilizar un parámetro de cursor OUTPUT

USE AdventureWorks;
GO

IF OBJECT_ID ( ‘dbo.uspCurrencyCursor’, ‘P’ ) IS NOT NULL
       DROP PROCEDURE dbo.uspCurrencyCursor;
GO

CREATE PROCEDURE dbo.uspCurrencyCursor
                   @CurrencyCursor
CURSOR VARYING OUTPUT
AS

SET @CurrencyCursor = CURSOR FORWARD_ONLY STATIC FOR
               SELECT CurrencyCode, Name
              FROM Sales.Currency;

OPEN @CurrencyCursor;
GO

–Recuperando las filas desde el cursor
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT
;

FETCH NEXT FROM @MyCursor;
WHILE (@@FETCH_STATUS = 0
)
BEGIN
;
   
FETCH NEXT FROM @MyCursor
;
END;

CLOSE @MyCursor;
DEALLOCATE @MyCursor
;
GO

Aunque yo particularmente no utlizó este tipo de parametro en mis SPs de desarrollo, bueno las lineas anteriores para la demo; siempre es bueno utilizar: Begin Try … End Try Begin Catch … End Catch dentro de los SPs.

 

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.

 

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.

4 Responses to Stored Procedures y Functions en SQL Server 2005

  1. Marco Antonio says:

    Tienen un stored que haga esto.· Consultar de las tablas de facturación datos de la cabecera de las facturas y detalle de las mismas.· Consolidarlo en una tabla nueva.· Utilizar una bandera para saber qué datos ya fueron copiados a la nueva tabla.· Si se puede manipular las tablas con los datos de facturación (para bandera de datos copiados a nueva tabla).Saludos

  2. Agustin says:

    Que tal, tengo una app web en .NET que dispara consultas en SQL en un servidor web, el tema es que algunas consultas demoran demasiado y me cierran la sesion en la app (timeout), cuando esto pasa, en el servidor queda el procesador prendido del servicio de sql (calculo que seguira haciendo la consulta), hay alguna forma de hacer que en el timeout de la app tire un cierre del proceso del sql para liberar el procesador? tomando en cuenta que la sesion de cerro, ya no hace falta el resultado de la consulta
     
    Gracias

  3. Jonnathan says:

    Primero, si utilizas ya sea SqlCommand, DbCommand o IDbCommand, ellos tienen una propiedad llamada CommandTimeout que ayuda mucho para el manejo del timeout, te digo por la experiencia que tengo con el tema de Queries para Reporting, y tmb transactions con ADO.NET desde el Net FrameWork 1.1,ahora dentro de cualquier metodo del Business Data Layer, es aconsejable poner el try catch con su respectivo Finally, donde puedes validar el estado de tu objeto connection (ya sea de un IDbConnection, DbConnection o SqlConnection), contra el enum ConnectionState que tiene entre sus items, Executing, Broken, etc, tmb podrias usar el using que es válido, pero no la usó.
     
    El tema de Session en ASP.NET son manejables en el Global.ASAX, podrias manejar ahi el tema.
    te recomiendo utlizar LinQ to SQL q esta disponible desde el Net FrameWork 3.0, y por supuesto el System.Transactions que viene con el Net FrameWork 2.0, bueno nos vemos,
    Cualquier Consulta de ASP.NET 3.5, LinQ ( ya se apara XML, Datasets, Object, SQL, o Entity), C# en si, de Windows Communication Foundation, Web Services, SQL SERVER 2005, de Analysis Services, Integration Services y Reporting Services estaré gustoso en verlo
     
    Jonnathan
    Desarrollar Software y/o Aplicaciones no es solo por trabajo, sino una pasion!!!

  4. victor says:

    3. 5 tipos : Sistema, Local, Temporal, Remotos, Extendidos de

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