Getting started with Stored Procedures in SQL Server
By: Greg Robidoux | Updated: 2020-07-30 | Comments (15) | Related: More> Stored Procedures
Problem
he estado usando SQL Server durante algún tiempo, pero todo el código que se emite contra la base de datos está incrustado en el código de la aplicación. Sé que puede crear procedimientos almacenados, pero no estoy exactamente seguro de por dónde empezar o lo que necesito para implementar procedimientos almacenados.,
solución
Los procedimientos almacenados no son más que un lote de sentencias T-SQL que se almacenan en la base de datos. En lugar de tener que emitir varias sentencias desde su aplicación, puede emitir un comando para llamar al procedimiento almacenado para hacer una cadena de trabajo en lugar de una sola sentencia. Además, dado que el código se almacena en la base de datos, puede emitir el mismo conjunto de código una y otra vez, incluso desde diferentes aplicaciones o una ventana de consulta. Para comenzar, el resto de este consejo analiza algunos procedimientos almacenados de muestra y cómo puede comenzar y construir sobre ellos.,
los siguientes ejemplos muestran lo sencillo que es crear procedimientos almacenados. Todos estos ejemplos utilizan la base de datos AdventureWorks, pero estos ejemplos deberían ser bastante sencillos para que pueda aplicar estos conceptos a sus propias bases de datos y aplicaciones.
ejemplo 1 – procedimiento almacenado simple
Este primer ejemplo crea un procedimiento almacenado simple que obtiene el registro TOP 1 de la persona.Tabla de contactos.
CREATE PROCEDURE uspGetContact AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact
después de que se haya creado lo anterior, use el comando a continuación para ejecutar este storedprocedure.,
EXEC uspGetContact
Este es el resultado de esta primera consulta.
Ejemplo 2 – procedimiento almacenado con un parámetro
Este siguiente ejemplo es una modificación del primer ejemplo, pero esta vez se agrega un parámetro que se pasa al procedimiento para seleccionar dinámicamente los registros.En lugar de usar CREATE PROCEDURE, estamos usando ALTER PROCEDURE para modificar el procedimiento que creamos en el Ejemplo 1 en lugar de soltarlo primero y luego recrearlo.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS SELECT TOP 1 ContactID, FirstName, LastName FROM Person.Contact WHERE LastName = @LastName
a continuación se muestran dos formas diferentes de ejecutar el procedimiento almacenado. El primer ejemplo simplemente pasa el valor del parámetro que queremos usar y el segundo ejemplo también incluye el nombre del parámetro junto con el valor. Puede ejecutar el procedimiento almacenado con uno de estos comandos.
EXEC uspGetContact "Alberts" EXEC uspGetContact @LastName="Alberts"
Este es el resultado de esta primera consulta.,
Ejemplo 3 – procedimiento almacenado con un parámetro y el parámetro de salida
En este ejemplo tenemos un parámetro de entrada, así como un parámetro de SALIDA.El parámetro output se utilizará para devolver el ContactID que estamos buscando en el procedimiento almacenado. Este parámetro de salida se utilizará para seleccionar elpersons ContactID, FirstName y LastName junto con cualquier registro de dirección para esta persona.,
de nuevo estamos alterando el procedimiento almacenado uspGetContact y luego, en segundo lugar, ejecutamos el siguiente conjunto de código que ejecuta el procedimiento uspGetContact y, a continuación, basado en el valor devuelto que obtiene, también consultará el nombre de las personas y la información de dirección.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName
después de que el procedimiento almacenado haya sido alterado, ejecute el siguiente bloque de código. Esto ejecutará el procedimiento almacenado anteriormente y si el ContactID tiene un valor, también devolverá la información de la persona y la dirección.
DECLARE @ContactID INT SET @ContactID = 0 EXEC uspGetContact @LastName="Smith", @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END
Este es el resultado.,
Ejemplo 4 – procedimiento almacenado usando la instrucción RAISERROR
en este ejemplo combinamos los dos pasos del Ejemplo 3 en un procedimiento almacenado.El primer paso es obtener el ContactID y luego la segunda parte del procedimiento buscará el nombre de las personas y la información de la dirección. También agregamos código para usar la sentencia RAISERROR para devolver un error si no se encuentran registros.
esto se ejecuta dos veces para mostrar cómo se ve cuando se encuentran datos y cuando no se encuentran datos., La instrucción RAISERROR se puede usar para controlar cómo su aplicación no maneja datos o cualquier otro error que pueda ocurrir.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName IF @@ROWCOUNT > 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
EXEC uspGetContact @LastName="Walters"
Este es el resultado.
EXEC uspGetContact @LastName="Job"
Este es el resultado cuando no se encuentran datos.
ejemplo 5 – procedimiento almacenado con una llamada separada a procedimiento almacenado
Aquí hay otro ejemplo donde tenemos dos procedimientos almacenados., El primer storedprocedure uspfindcontact busca el primer registro que tiene un registro de dirección y luego devuelve el ContactID al procedimiento almacenado que llama para mostrar nuevamente la información de la persona y la dirección.
CREATE PROCEDURE uspFindContact @LastName NVARCHAR(50), @ContactID INT output AS SELECT TOP 1 @ContactID = c.ContactID FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.LastName = @LastName
el siguiente código hace un alter del procedimiento almacenado uspgetcontact que llama a uspfindcontact y devuelve los conjuntos de Registros.
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS DECLARE @ContactID INT SET @ContactID = 0 EXEC uspFindContact @, @ OUTPUT IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
EXEC uspGetContact @LastName="Walters"
Este es el resultado.
EXEC uspGetContact @LastName="Job"
Este es el resultado.,
ejemplo 6 – procedimiento almacenado con comentarios
Este último ejemplo toma el procedimiento almacenado uspGetContact y agrega comentarios al código para que pueda ver cómo funcionan los comentarios dentro de un procedimiento almacenado.
Los comentarios se pueden hacer de dos maneras
- usando/
- usando / * para comenzar el bloque de comentarios y * / para terminar el bloque de comentarios.
aparte de eso nada más ha cambiado en el procedimiento almacenado.,
ALTER PROCEDURE uspGetContact @LastName NVARCHAR(50) AS /* This is a sample stored procedure to show how comments work within a stored procedure */ -- declare variable DECLARE @ContactID INT -- set variable value SET @ContactID = 0 -- execute stored proc and return ContactID value EXEC uspFindContact @, @ OUTPUT -- if ContactID does not equal 0 then return data else return error IF @ContactID <> 0 BEGIN SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID = @ContactID SELECT d.AddressLine1, d.City, d.PostalCode FROM HumanResources.Employee a INNER JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID INNER JOIN Person.Contact c ON a.ContactID = c.ContactID INNER JOIN Person.Address d ON b.AddressID = d.AddressID WHERE c.ContactID = @ContactID END ELSE BEGIN RAISERROR ("No record found",10,1) END
estos son ejemplos bastante simples, pero esperamos que esto le dé una idea de lo fácil que es crear procedimientos almacenados para SQL Server. Si puede ejecutar un estado SELECT desde una ventana de consulta o desde su aplicación, puede ejecutar un procedimiento guardado tan fácilmente como se muestra arriba.,
próximos pasos
- Si aún no está utilizando procedimientos almacenados, esperemos que esto le dé alguna idea de lo que necesita hacer para comenzar a usarlos
- Como se mencionó, estos son ejemplos bastante simples, pero casi cualquier cosa que pueda hacer con un lote de instrucciones se puede combinar en un procedimiento almacenado y luego se puede usar una y otra vez para sus aplicaciones.,
- salida thestored procedimiento tutorial para obtener más ejemplos
Última actualización: 2020-07-30
Sobre el autor
Greg Robidoux es el Presidente de Edgewood Soluciones y co-fundador de MSSQLTips.com.Ver todos mis consejos
- Más de la Base de datos del Desarrollador Consejos…