SQL Stored Procedures

From WikiOD

Remarks[edit | edit source]

Stored Procedures are SQL statements stored in the database that can be executed or called in queries. Using a stored procedure allows encapsulation of complicated or frequently used logic, and improves query performance by utilizing cached query plans. They can return any value a standard query can return.

Other benefits over dynamic SQL expressions are listed on Wikipeida.

Create and call a stored procedure[edit | edit source]

Stored procedures can be created through a database management GUI (SQL Server example), or through a SQL statement as follows:

** Define a name and parameters
CREATE PROCEDURE Northwind.getEmployee
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   
** Define the query to be run
SELECT FirstName, LastName, Department  
FROM Northwind.vEmployeeDepartment
WHERE FirstName = @FirstName AND LastName = @LastName  
AND EndDate IS NULL;

Calling the procedure:

EXECUTE Northwind.getEmployee N'Ackerman', N'Pilar';
** Or  
EXEC Northwind.getEmployee @LastName = N'Ackerman', @FirstName = N'Pilar';  
GO  
** Or  
EXECUTE Northwind.getEmployee @FirstName = N'Pilar', @LastName = N'Ackerman';  
GO

Credit:Stack_Overflow_Documentation