Procedimientos almacenados SQL Server

Procedimientos almacenados con SQL Server 2005

Para iniciar este post, mencionemos que es un procedimiento almacenado, un procedimiento almacenado es un elemento de base de datos reutilizable almacenado que realiza alguna operación en la base de datos. Un procedimiento almacenado contiene código SQL que puede, entre otras cosas, insertar, actualizar o eliminar registros.

Los procedimientos almacenados también pueden alterar la estructura de la base de datos. Por ejemplo, pueden utilizar un procedimiento almacenado para añadir una columna de tabla o borrar una tabla. De la misma manera un procedimiento almacenado puede llamar a otro procedimiento almacenado, así como aceptar entradas y devolver múltiples valores al procedimiento llamado en forma de parámetros de salida.

En este ejemplo realizaremos un procedimiento sencillo solo para mostrar cómo se hacen y la configuración del CLR para la integración del mismo con SQL Server 2005, iniciaremos por abrir Visual Studio (Inicio->Todos los Programas->Microsoft Visual Studio 2005), y seleccionamos nuevo proyecto (Archivo->Nuevo->Proyecto...), en la plantilla de Nuevo Proyecto seleccionamos en Tipo de Proyecto Database y en Plantillas damos clic a Proyecto de SQL Server y damos un nombre, en mi caso el proyecto se llama SQLServerHolaMundo

Lo que vamos hacer con Visual Studio es crear nuestro procedimiento almacenado en código administrado para después poder hacer un deploy hacia la base que vamos a usar, para esto necesitamos hacer una conexión con SQL Server 2005 Express, en la que usare mi servidor local, y vamos a usar la base de datos creada en este post Cómo crear una base de datos con SQL Server 2005 Express que se llama Demo_bd



Luego probamos la conexión y damos clic en Aceptar



Esta conexión es básicamente para establecer la comunicación entre Visual Studio 2005 y SQL Server 2005, después de esto Visual Studio nos pregunta si queremos habilitar la depuración SQL/CLR de esta conexión, damos clic en Si




El siguiente paso es crear nuestro Procedimiento Almacenado, para esto damos clic derecho a nuestro proyecto en el Explorador de soluciones->Agregar->Procedimiento Almacenado, al que llamaremos "HolaMundo.cs"





Ya en esta parte lo primero que hacemos es utilizar la propiedad nombre, para asignarle un nombre a nuestro SqlProcedure, que en mi caso lo llamare de igual manera HolaMundo (aunque es no es necesario que sea así, es decir; que tenga el mismo nombre que el procedimiento almacenado), el código de este procedimiento queda de esta forma:



public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure (Name="HolaMundo")]

public static void HolaMundo()

{

SqlContext.Pipe.Send("Hola Mundo desde SQL Server 2005 Express");

// Inserte el código aquí

}

};


En donde utilizamos el SqlContext que es el contexto que tenemos contra SQL Server, y una vez instanciado a través de SqlContext podemos tener comunicación con SQL Server, y utilizamos el método Pipe, que es nuestro canal de comunicación, y utilizamos el método send para mandar un mensaje. Después de esto generamos una solución para verificar que todo está bien, y luego realizamos un deploy de nuestro ensamblado dentro de Visual Studio 2005.

Una parte importante es el habilitar el CLR ya que sin no realizamos esta acción al generar nuestro deploy nos genera un error que nos dice lo siguiente:

Error 1 La ejecución de código en .NET Framework está deshabilitada. Establezca la opción de configuración "clr enabled" y reinicie el servidor. SqlServerHolaMundo

Entonces para habilitar nuestro CLR abrimos Configuración de Superficie de SQL Server 2005 y damos clic en Configuración de superficie para características, en donde nos muestra los diferentes componentes y la posible configuración de sus características, en esta parte seleccionamos Integración del CLR y lo habilitamos y damos clic en Aceptar



Mientras se genera el deploy abrimos nuestro Microsoft SQL Server Management Studio Express realizamos nuestra conexión, en mi caso con mi servidor local



Una vez ya dentro de SQL Server Management Studio podemos localizar el ensamblado que acabamos de crear, al igual que en el Visual Studio en el explorador de Servidores, para poder localizar este ensamblado en SQL Server Management nos ubicamos en el Explorador de Objetos->Bases de datos->Demo_bd->Programación->Ensamblados aquí se encuentra nuestro nuevo ensamblado que se llama SqlServer, y nuestro procedimiento almacenado se encuentra en Explorador de Objetos->Bases de datos->Demo_bd->Programación->Procedimientos almacenados que se llama dbo.HolaMundo.



Ya aquí podemos ejecutar nuestro proceso almacenado, para esto damos clic en Nueva consulta indicamos que usamos la base de datos Demos_db y ejecutamos con F5 y este es el resultado




Ya con esto sabemos con crear un procedimiento almacenado y ejecutarlo, pero que pasa si queremos llamar un una clase externa de .NET. Por ejemplo imaginemos que queremos escribir en un archivo de texto la suma de dos números, (este ejemplo es sencillo solo es para mostrar de qué manera se hace y activar la propiedad externa de nuestra base de datos), bien para esto en el Visual Studio 2005 nos dirigimos a las propiedades de nuestro proyecto dando clic derecho y en la parte de Base de datos en el Nivel de permisos activamos la propiedad Externo, de esta manera podemos integrar clases de .NET sin ningún problema como ejemplo en nuestro procedimiento HolaMundo agregamos el espacio de nombres System.IO y modificamos el código para que quede de esta forma.



public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure (Name="HolaMundo")]

public static void HolaMundo(SqlInt32 resultado, SqlString cadena )

{

// Inserte el código aquí

using(StreamWriter escribe = File.CreateText(@"C:\Demo\demo.txt"))

{

int numA = 1;

int numB = 4;

resultado = numB + numA;

escribe.Write(cadena.ToString());

escribe.Write(resultado.ToString());



}

SqlContext.Pipe.Send(cadena.ToString() + resultado.ToString());



}


Después de agregar este código Generamos de nuevo la solución, y al tratar de hacer de nuevo un Deploy nos genera un error el Visual Studio ya que debemos activar la propiedad de la base de datos trustworthy y para esto en el SQL Server Management Studio ejecutamos la siguiente instrucción:



alter database Demo_bd

set trustworthy on


Después de esto ya realizamos nuestra Implementación (Deploy) desde el Visual Studio 2005 y vemos que ya no marca ningún error, lo último es ejecutar de nuevo nuestro procedimiento almacenado, con los parámetros correspondientes y podemos ver que funciona de manera correcta, nos muestra el mensaje y la suma, ahora podemos ir a nuestra carpeta que creamos en la unidad C que se llama Demo y observar que efectivamente nos genero el archivo de texto.



Creo que eso es todo es este post, recuerden que no solo podemos agregar clases de .NET, podemos agregar muchas más cosas, incluso XML Web Service, que ya en post pasados publique como hacerlos. Espero que les ayude este post sobre procedimientos almacenados, repito es sencillo pero ilustrativo, nos vemos en el siguiente.