SQL SERVER – Crear un procedimiento almacenado

Para crear procedimientos almacenados en SQL Server se debe hacer uso del lenguaje de programación T-SQL. Este lenguaje permite crear scripts que son ejecutados por el servidor de base de datos con la ventaja de que el motor de base de datos optimiza las sentencias SQL contenidas en él, ganando con ello mejores estadísticas al ser ejecutados. Para esta entrada usaré la versión 2005 pero lo aquí expuesto es válido tanto para la 2008 como para la 2012 o y 2014.

*** Importante. En MS SQL Server 2000 aplica lo aquí expuesto pero debido a que el administrador es distinto, los pasos a seguir cambian un poco.

El procedimiento almacenado que vamos a crear nos va a permitir devolver una lista de productos que cumplan con un determinado punto de reorden o deberá devolver la lista completa de productos cuando el punto de reorden sea igual a cero. La base de datos que usaremos para este artículo es la proporcionada por Microsoft: AdventureWorks. Si no la tienes en tu servidor puedes seguir los pasos descritos en el artículo: Attach y Dettach en SQL Server.

Para entrar en materia, lo primero por hacer será ejecutar nuestro SQL Magnagement Studio y una vez que nos hayamos autenticado seleccionaremos la base de datos AdventureWorks, expandiremos el árbol y seleccionaremos la rama Programmability. Expandiremos nuevamente esta rama y seleccionaremos la rama Stored Procedures. Al tener seleccionada esta última rama daremos click con el botón derecho del mouse y seleccionaremos la opción de menú:  New Stored Procedure. Tal y como se muestra en la siguiente imagen.

sql_sp

 

Después de haber seleccionado la opción para crear un nuevo procedimiento almacenado, SQL Server abre una nueva pantalla donde nos genera la estructura básica de un procedimiento almacenado como la que se muestra a continuación:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
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

En esta estructura podremos remplazar los valores por default por aquellos que necesitamos para nuestro propósito. Lo primero será modificar el nombre y después vamos a definir la lista de parámetros de nuestro procedimiento como sigue:

-- ================================================
-- Procedimiento almacenado que devuelve productos
-- filtrados por su punto de reorden
-- Author:		 Edgar Ramírez
-- Create date:  2013-07-17
-- Description:	Procedimiento almacenado para blog
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE SPProductosPtoReord
	(@PtoReorden int)
AS
BEGIN

END
GO

Vale la pena recomendar que se incluya información sobre el procedimiento almacenado pues servirá de guía para quienes modifiquen el código fuente del procedimiento.   Ahora lo que sigue es agregar los bloques de código PLSQL que devuelvan la lista de productos de acuerdo al punto de reorden, quedando nuestro procedimiento almacenado de la siguiente manera:

CREATE PROCEDURE SPProductosPtoReord
	(@PtoReorden int)
AS
BEGIN

  If @PtoReorden = 0 
    begin
      Select ProductID, Name, ProductNumber, MakeFlag, 
             FinishedGoodsFlag, Color, ReorderPoint
        From Production.Product;
    End 

  If @PtoReorden <> 0 
    Begin
       Select ProductID, Name, ProductNumber, MakeFlag, 
              FinishedGoodsFlag, Color, ReorderPoint
         From Production.Product
        where ReorderPoint =  @PtoReorden;
    End 

END

Una vez que tenemos nuestro procedimiento, compilamos presionando F5 y si todo es correcto nos aparecerá el mensaje: “Comandos completados correctamente”. Ahora vamos a ejecutar nuestro procedimiento almacenado proporcionando un punto de reorden para obtener los productos que coincidan, para ello ejecutaremos la siguiente sentencia SQL:

Exec SPProductosPtoReorden 750

Ejecutamos la sentencia presionando F5 y obtendremos lo siguiente:

 

sql_sp_execute

 


Otros temás de interés:

3 Trackbacks / Pingbacks

  1. Conversaciones sobre Programación. Build 2013.03 | Linea de Codigo
  2. Linea de Codigo Lenguajes de Programación | Los Infos
  3. Delphi – Ejecutar procedimientos almacenados de SQL Server | Blog del Poli

Deja un comentario