Inicio > ADO.NET, Ejemplos, SQL Serveer 2008, SQL Server, SQL Server 2005 > [SQL]Procedimientos almacenados paso a paso

[SQL]Procedimientos almacenados paso a paso

Un procedimiento almacenado (store procedure) no es más que una secuencia ordenada de instrucciones T-SQL, que pueden recibir y proporcionar parámetros provistos por el usuario y se pueden guardar en el servidor con un nombre, para posteriormente se invocados y ejecutados, por lo regular desde una aplicación (Escritorio o Web). Desde la versión 2005, se incorpora la posibilidad de utilizar procedimientos almacenados usando el CLR de .NET. Es decir tenemos dos tipos de procedimientos almacenados.

Un procedimiento almacendado CLR es una referencia a un método de un ensamble (dll) de .NET Framework que puede aceptar y devolver parámetros suministrados por el usuario.

Ventajas de usar SP

  • Compilación: La primera vez que se invoca un SP, el motor lo compila y a partir de ahí, se sigue usando la versión compilada del mismo, hasta que se modifique o se reinicie el servicio de SQL. Esto siginifica que se tendrá un mejor rendimiento que las consultas directas que usan cadenas con las instrucciones T-SQL, que se compilan cada vez que se invocan.
  • Automatización: si tenemos un conjunto de instrucciones T-SQL, las cuales queremos ejecutar de manera ordenada, un SP es la mejor manera de hacerlo.
  • Administración: cuando realizamos aplicaciones con un gran numero de lineas de código, y queremos hacer cambios, solo implica modificar un SP y no toda la aplicación, lo que significa solo cambiamos los SP en el servidor y no tenemos que actualizar la aplicación en todos los equipos cliente.
  • Seguridad: una parte importante es que a los usuarios de nuestra aplicación, solo les proporcionamos los permisos para ejecutar los procedimientos almacenados y no el acceso a todos los objetos de la base. Es decir, si en nuestra aplicación encuentran una vulnerabilidad como SLQ Injection no se podrá explotar ejecutando SQL directamente.
  • Programabilidad: Los SP admiten el uso de variables y estructuras de control como IF, Bucles,  Case, etc. además del manejo de transacción y permite controlar excepciones. Y cuando trabajamos con SP CLR podemos hacer uso de cualquier lenguaje .NET como lo son C# y VB.NET.
  • Trafico de Red:  Pueden reducir el trafico de la red, debido a que se trabaja sobre el motor (en el servidor), y si una operación incluye hacer un trabajo de lectura primero y en base a eso realizar algunas operaciones, esos datos que se obtienen no viajan por la red.

Creando un Procedimiento almacenado

Para crear un procedimiento almacenado solo basta con ir a la base de datos desplegar el árbol hasta la parte de programación y luego en procedimientos almacenados y damos clic derecho en nuevo procedimiento almacenado como se ve en la siguiente figura:

image

Lo cual genera el siguiente código:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Es aquí en donde editamos nuestro procedimiento como mejor nos interese, en este caso usando la base de datos Northwind, crearemos un SP sencillo que queda más o menos así:

USE [Northwind]
GO

CREATE PROCEDURE [dbo].[GetAllEmployees]
    -- Add the parameters for the stored procedure here
    @LastName nvarchar(50),
    @FirstName nvarchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT  LastName, FirstName, Title
    FROM dbo.Employees
    WHERE FirstName = @FirstName AND LastName = @LastName
END

En este sencillo ejemplo, sólo devuelve el empleado especificado (nombre y apellidos), y el titulo a partir de una vista. Este procedimiento almacenado acepta coincidencias exactas de los parámetros pasados.

Para ejecutar el código debemos hacerlo de esta manera:

EXEC dbo.GetAllEmployees 'Davolio', 'Nancy'

Cómo lo consumimos desde una aplicación en .NET, pues de esta manera:

static void Main(string[] args)
        {
            //Creamos una nueva conexion.
            SqlConnection miConn = new SqlConnection("Data Source=NORTABLEPC\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True");

            //Creamos un nuevo comando
            SqlCommand miComm = new SqlCommand();
            //Le asignamos la conexion.
            miComm.Connection = miConn;
            //especificamos que el comando es un stored procedure
            miComm.CommandType = System.Data.CommandType.StoredProcedure;
            //y escribimos el nombre del stored procedure a invocar
            miComm.CommandText = "dbo.GetAllEmployees";
            //Creamos un nuevo parametro
            SqlParameter paramLastName = new SqlParameter();
            paramLastName.ParameterName = "@LastName";
            paramLastName.SqlDbType = System.Data.SqlDbType.NVarChar;
            paramLastName.Value = "Davolio";

            miComm.Parameters.Add(paramLastName);

            SqlParameter paramFirstName = new SqlParameter();
            paramFirstName.ParameterName = "@FirstName";
            paramFirstName.SqlDbType = SqlDbType.NVarChar;
            paramFirstName.Value = "Nancy";
            miComm.Parameters.Add(paramFirstName);
            //Y los agregamos a la coleccion de parametros del comando myComm.Parameters.Add(myParam) 'Creamos un nuevo DataAdapter con nuestro comando.
            SqlDataAdapter miDA = new SqlDataAdapter(miComm);
            //Creamos un dataset para soportar los datos devueltos por el stored procedure
            DataSet EmpleadosDS = new DataSet();
            //Pedimos al Data Adapter que llene el dataset (Esto llama a nuestro comando)
            miDA.Fill(EmpleadosDS);
            //Y lo mostramos por pantalla

            foreach (DataRow row in EmpleadosDS.Tables[0].Rows)
            {
                Console.WriteLine(row["LastName"].ToString() + " " + row[1].ToString() + " " + row[2].ToString());
            }
        }

Procedimientos Almacenados con CLR

En Common Language Runtime (CLR), los procedimientos almacenados se implementan como métodos estáticos públicos en una clase de un ensamblado de Microsoft .NET Framework. El método estático se puede declarar como void o puede devolver un valor entero. Si devuelve un valor entero, éste se trata como el código devuelto desde el procedimiento. Los parámetros que se pasan a un procedimiento almacenado CLR pueden ser cualquiera de los tipos nativos de SQL Server que tengan un equivalente en código administrado. Para que la sintaxis Transact-SQL cree el procedimiento, estos tipos se deben especificar con el equivalente del tipo nativo de SQL más adecuado.

Ejemplo:

Para crear un SP con integración del CLR vamos a usar VS, y creamos un Proyecto de tipo SQL, al que llame StoreProcedure_Demo, lo primero que hace Visual Studio es preguntar el origen de datos, como se ve en la figura siguiente:

image En donde seleccionamos Microsoft SQL Server (SqlClient) y damos clic en continuar. Ahora toca el turno de elegir el Servidor

imageEn donde yo selecciono el servidor NORTABLEPC\SQLEXPRESS, y la base de datos Northwind, luego verifico la conexión y si todo va bien, por ultimo nos pedirá que si queremos habilitar la integración con el CLR, damos clic y listo. Luego damos clic derecho en nuestro proyecto Agregar->Procedimiento almacenado, como se ve en la figura:

image

Al procedimiento lo llamamos: GetEmpleado, damos y damos clic en Aceptar. Ahora vamos a escribir el siguiente código:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure (Name="GetEmpleado")]
    public static void GetEmpleado()
    {
        // Inserte el código aquí
        SqlContext.Pipe.Send("Hola Mundo! son las : " + System.DateTime.Now.ToString() + "\n");
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            SqlCommand command = new SqlCommand("SELECT LastName, FirstName FROM Employees", connection);
            SqlDataReader reader = command.ExecuteReader();
            SqlContext.Pipe.Send(reader);
        }

    }
};

En el código anterior solo mandamos un mensaje, y devolvemos un select sencillo de los empleados, Bien para que esto funcione, debemos generar una solución de nuestro proyecto y después una implementación del mismo, esto permite que automáticamente se envié la dll a nuestra base de datos con el nombre de nuestro proyecto, y al mismo tiempo agrega el procedimiento llamado GetEmpledo

image

Ahora solo nos resta ejecutar nuestro procedimiento con la siguiente instrucción:

EXEC dbo.GetEmpleado

Lo interesante de esto, es que una vez que ya hemos llamado al SP, cuando se ejecute de nuevo, ya no se compilara instrucción por instrucción solo se llama al dll y listo, el resultado de ejecutarlo es el siguiente, por un lado manda el resultado y por otro el mensaje de “hola mundo” con la hora de la maquina, como se ve en la figura:

image image

Hasta aquí dejamos este post, ya veremos en otro como ejecutar dichos procedimientos almacenados desde una aplicación. Espero que les sea de utilidad este post  y recuerden si lo creen conveniente tomen 5 min, para votar por este blog en la siguiente página : http://www.blogit.ms/ , voten por https://mspnor.wordpress.com

Saludos!!!

Etiquetas de Technorati: ,,

About these ads
  1. Nauj
    noviembre 11, 2008 en 12:31 am | #1

    En realidad esta bien.. nadamas que no tendras un ejemplo con delphi 2006,
    pero muy bueno ¡¡

  2. noviembre 11, 2008 en 2:41 pm | #2

    Amigo Nauj,

    hace años que no uso delphi, pero suena interesante como usar la versión para .net de este y ver como podemos trabajar los prosedimientos almacenados, me dare a la tarea de intentarle y luego cuento como va.

    Saludos!

  3. OMAr
    abril 4, 2010 en 3:38 am | #3

    HI! sta bueno pero no podrias poner un ejemplo donde crees una BD en sql y crees un procedimiento almacenado que te inserte en esa BD desde visual.net

    • Keros
      mayo 18, 2012 en 4:20 pm | #4

      y porque mejor no lo haces tu! y de paso haces un aporte…. no esperes que el resto haga las cosas por ti.!

  4. Carmelo
    mayo 19, 2010 en 3:43 pm | #5

    tu pagina no me ha servido..
    pon algo de triggers…
    cabron… uno se mata buscando informacion y no ofreces ni madres…

    pd. no hagas copy-paste de la wikipedia…

    • mayo 22, 2010 en 2:59 pm | #6

      que onda Carmelo,

      lamento que no te sirva la página, pero como te daras cuenta el blog no se espesializa en SQL, si buscas algo de Triggers seguro los encuentras en MSDN, Technet o la Wikipedia, y sobre lo que comentas que te matas buscando información me parece que tus key words no son lo suficientemente buenas para encontrar lo que buscas.

      Y el copy-paste del post no fue de wikipedia, fue de un libro de MS, pero gracias por el consejo.

      Saludos.

    • Jose
      junio 3, 2010 en 12:01 pm | #7

      Pues deja la direccion de tu Blog para ver que tan bueno eres ofreciendo informacion GRATUITA para ayudar a los demas. ademas el tema dice PROCEDIMIENTOS ALMACENADOS no TRIGGERS

      Por otra parte al propietario del blog, me parece la informacion muy util, entendible y precisa, yo programo en Powerbuilder, asi que “porque no te pones unos ejemplos en powerbuilder que me resuelvan todo mi trabajo, porque tu pagina no sirve bla bla bla y pon informacion acerca de los viajes espaciales que me mato buscando informacion de viajes espaciales en powerbuilder y no hay nimadres de eso ” jaja :P

      a gente perezosa…

      Muchas gracias
      Exitos y Saludos

      • mayo 4, 2011 en 2:46 am | #8

        chinga tu madree puto wannabe
        ni tu comentario me sirvio
        vales pura madre wey
        vete a la chingada

    • Tu padre
      abril 8, 2011 en 3:58 am | #9

      tu pinche comentario no me sirvió de nada Carmelo, pregunta algo util y así no pregunto nada yo, ademas copiaste y pegaste los tres puntos al final de tus frases (menos el primero) uno que se pone a leer todos los comentarios a ver que hay y tu maldita pregunta no es de importancia.

    • Keros
      mayo 18, 2012 en 4:23 pm | #10

      Y que mas quieres DICE “PROCEDIMIENTOS ALMACENADOS”, pesazo de idiota!, porque mejor no te esfuerzas y creas tu tema para ayudar! flojo!

  5. fransk
    agosto 10, 2010 en 4:56 pm | #11

    gracis de verdad, es una guiita muy util no ?…..

  6. AdriAna Mejia
    septiembre 8, 2010 en 2:39 pm | #12

    kf

  7. Lobch
    noviembre 13, 2010 en 12:19 am | #13

    Buen post, simple y concreto intente votar en BlogIT pero muy complicado

  8. enero 28, 2011 en 9:07 pm | #14

    Muy buen post.. lo entendi.. bastante bien.. en mi blog apoyo este tipo de cosas para que los q nos cuesta un poquito aprendamos un poquito mas..

  9. jrdnew
    febrero 21, 2011 en 4:41 pm | #15

    Gracias que buen post, este ejemplo me es muy util.

  10. mayo 4, 2011 en 2:44 am | #16

    hey bola de jotos
    no me sirvio pa ni madre esta pinche paginas
    ta conmadre el tipo ya q se puede copiar pero explica mas los trigger punk ass motherfucker

  11. Linux
    agosto 1, 2011 en 3:14 am | #17

    ¿¿¿Microsoft =$$$?? NOOOOOOOOOOOOOO!!!

    MEJOR SOFTWARE LIBRE NO CRES???

  12. agosto 19, 2011 en 6:59 pm | #18

    Se nota que no tienen un buen cerebro los que comentan mal del post, es muy bueno y cualquier aportacion que dan los usuarios de la red siempr eayudan el algo a los interezados en aprender, si no sabes ni lo que buscas obviamente no encuentras lo que quieres. Saludos

  13. Grego
    septiembre 22, 2011 en 7:22 am | #19

    No entiendo como puede haber gente que se dedique a destruir insultando, en vez de constuir colaborando. El que no sabe lo que busca no entiende lo que encuentra

  14. Jj
    noviembre 5, 2011 en 6:29 pm | #20

    un saludo, tengo un problema con esto.. como hago para hacer que el procedimiento almacenado se guarde en mi base, por q no lo hace, siempre me guarda el script en otra parte.. y no me aparece como procedimiento almacenado en mi base

  15. juraye
    noviembre 13, 2011 en 3:01 pm | #21

    Los desarrolladores de Software debemos de ser muy imaginativos, creativos y todo esto llevarlo a un lenguaje de programacion que más dominamos, para los que no les sirve este blog, solo miran el arbol pero no el bosque, se equivocaron de profesión. Saludos

  16. Cin
    noviembre 22, 2011 en 3:34 pm | #22

    Excelente, justo lo que necesitaba. Gracias!!

  17. Joel
    noviembre 25, 2011 en 3:52 pm | #23

    muy clara la explicacion, muchas gracias y saludos

  18. Jose Valverde
    diciembre 31, 2011 en 7:04 pm | #24

    Hola amigo soy nuevo en esto tengo que hacer un procedimiento

    sp_Existe_Empleado , si el empleado existe en la tabla HumanResources.Employee debe desplegar el campo BirthDate pero si no existe se de enviar un mensaje que no existe el empleado utilizando la esctructura de CASE.

    de esta forma funciona pero no se como modificarlo para utilizar el Case, si me pueden ayudar por este medio o mi correo jvalverdech@gmail.com

    CREATE PROCEDURE sp_Existe_Empleado @IdEmpleado INT
    AS
    BEGIN
    IF EXISTS (SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IdEmpleado)
    BEGIN
    SELECT BirthDate FROM HumanResources.Employee
    WHERE EmployeeID = @IdEmpleado
    END
    ELSE
    BEGIN
    SELECT ‘EMPLEADO NO EXIXTE’
    END
    END

  19. Luis
    enero 17, 2012 en 3:33 pm | #25

    muy util para entender mas sobre este tema. Gracias

  20. Agomez
    enero 18, 2012 en 2:55 am | #26

    Excelente aportaciomn

  21. Marcelitro
    febrero 4, 2012 en 5:09 pm | #27

    Si esta ayuda la hubiese encontrado antes!
    Se deben de tomar el tiempo para leer y entender.

    Muchas gracias Norberto.

    PD:
    Nunca dejaras a todos contentos… disfruta de los logros de los que quieren aprender y reciben tu ayuda agradecidos.

  22. marzo 15, 2012 en 1:04 am | #28

    Gracias me sirvió, trabajo en una aplicacion de control de ingresos y salidas de personal y lo implemente. Un voto mas para tu sitio.

    Cuarto Nivel
    cuartonivel.comuf.com

  23. marzo 26, 2012 en 10:43 pm | #29

    buen blog amigo, espero que el mio sea igual de bueno algun dia

    http://dbasqlserver.wordpress.com/
    saludos

  24. Gradiz
    abril 21, 2012 en 5:15 am | #31

    Esta muy bueno tu post, me sirvio de mucho y entendi como trabajar con los procedimientos almacenados esta facil de entender, gracias por el aporte

  25. Boli
    junio 7, 2012 en 7:02 pm | #32

    esta muy bueno la explicacion seria bueno que nos explicaras como hacerlo en una aplicacion .net

  26. Mirage
    julio 3, 2012 en 6:48 am | #33

    GRAX!!!!!!!! =D

  27. Ani
    agosto 30, 2012 en 5:25 pm | #34

    hola a todos, tengo un inconveniente, tengo un stored procedure pero no genera el codigo de barras hijo y no se en donde esta mal ya que corre el mismo.
    El stored procedure es el siguiente:
    ALTER PROCEDURE [dbo].[spGrabarDetallebien]
    (
    @iddet int,–1
    @carga1 varchar(100),
    @carga2 varchar(100),
    @carga3 varchar(100),
    @carga4 varchar(100),
    @carga5 varchar(100),–10
    @carga6 varchar(100),
    @carga7 varchar(100),
    @carga8 varchar(100),
    @dircallepri varchar(60),–10
    @dirnumcalle varchar(15),
    @dircallesec varchar(60),
    @dirreferencia varchar(60),
    @biegrupo int,
    @biesubgrupo int,
    @biedetalle int,
    @bieprov int,
    @biecan int,
    @bieciu int,
    @biesec int,–20
    @carga9 varchar(100),
    @etiquetaP varchar(100),
    @codbarra varchar(20)–23
    )
    AS
    begin

    DECLARE @barrahijo varchar(25)
    set @barrahijo= @codbarra+’-’+@iddet

    –ID del futuro Registro

    If Exists(Select * From DETALLEBIEN Where DET_CODBARRAHIJO=@barrahijo )
    BEGIN transaction
    –DECLARE @seReDetbien int =0
    –select @seReDetbien = @seReDetbien+1 from DETALLEBIEN WHERE SOL_CODBARRA
    DECLARE @seReDetbien int;
    SELECT @seReDetbien= coalesce((select max(DET_ID) + 1 from DETALLEBIEN), 1)
    COMMIT

    INSERT INTO DETALLEBIEN
    (–DET_ID,
    SOL_CARGA1,SOL_CARGA2,SOL_CARGA3,SOL_CARGA4,SOL_CARGA5,SOL_CARGA6,SOL_CARGA7,SOL_CARGA8,SOL_CARGA9,
    BIE_ID3,BIE_ID2,BIE_ID1,UGE_ID4,UGE_ID3,UGE_ID2,UGE_ID1,
    SOL_DIRCALLEPRI,SOL_DIRNUMERO,SOL_DIRCALLESEC,SOL_DIRREFE,
    SOL_CODBARRA, DET_CODBARRAHIJO,SOL_PROS)
    VALUES
    (–@se,
    @carga1,@carga2,@carga3,@carga4,@carga5,@carga6,@carga7,@carga8,@carga9,
    @biegrupo,@biesubgrupo,@biedetalle,@bieprov,@biecan,@bieciu,@biesec,
    @dircallepri,@dirnumcalle,@dircallesec,@dirreferencia,
    @codbarra,@barrahijo,@etiquetaP)

    –end

    end

    GO

  28. octubre 17, 2012 en 9:49 pm | #35

    Howdy, i read your blog from time to time and i own a similar one and i was just wondering if you get a
    lot of spam comments? If so how do you prevent it, any plugin or anything you can suggest?
    I get so much lately it’s driving me crazy so any support is very much appreciated.

  29. enero 31, 2013 en 3:18 am | #36

    Greetings from Florida! I’m bored to tears at work so I decided to browse your website on my iphone during lunch break. I really like the information you present here and can’t wait to take a look when I get
    home. I’m amazed at how fast your blog loaded on my cell phone .. I’m
    not even using WIFI, just 3G .. Anyways, good site!

  1. No trackbacks yet.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

%d personas les gusta esto: