Stored procedures are a powerful part of SQL Server. They can assist programmers and administrators greatly in working with the database configuration and its data.
A stored procedure is a precompiled group of Transact-SQL statements, and is saved to the database (under the "Stored Procedures" node). Programmers and administrators can execute stored procedures either from the SQL Server Management Studio or from within an application as required.
Transact-SQL, which is based on SQL (Structured Query Language), is the programming language used to interface between applications and their databases. Transact-SQL is a relatively easy language to learn and I highly recommend becoming familiar with it.
Benefits of Stored Procedures
Benefit | Explanation of benefit |
---|---|
Modular programming | You can write a stored procedure once, then call it from multiple places in your application. |
Performance | Stored procedures provide faster code execution and reduce network traffic.
|
Security | Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way. |
Creating a Stored Procedure
You create stored procedures in the SQL Server Management Studio using the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure.
CREATE PROCEDURE StoredProcedureName AS
...
The following code creates a stored procedure called "MyStoredProcedure":
CREATE PROCEDURE MyStoredProcedure AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC
Once you run this code in the SQL Server Management Studio, the stored procedure is created and appears under the "Stored Procedures" node.
Modifying a Stored Procedure
If you need to modify an existing stored procedure, you simply replace the CREATE with ALTER.
ALTER PROCEDURE MyStoredProcedure AS
...
Running a Stored Procedure
You can run a stored procedure by using EXECUTE or EXEC. For example, to run the above stored procedure, type the following:
EXEC MyStoredProcedure
If the stored procedure has spaces in its name, enclose it between double quotes:
EXEC "My Stored Procedure"
If your stored procedure accepts any parameters, they are placed after the procedure name:
EXEC MyStoredProcedure @ParameterName="MyParameter"
So, here's an example:
EXEC SalesByCategory @CategoryName ="Beverages"
Using The GUI
You can also use the graphical user interface to initiate the execution of a stored procedure.
To initiate a stored procedure this way:
- Navigate to the stored procedure
- Right click on the stored procedure and select "Execute Stored Procedure...":
- A dialog will appear. Enter your chosen parameter values etc:
- Click "OK"
- SQL Server will now generate the SQL code and execute the stored procedure:
Parameters
A parameter is a value that your stored procedure uses in order to perform it's task. When you write a stored procedure, you can specify the parameters that need to be supplied from the user. For example, if you write a stored procedure to select the address details about an individual, your stored procedure needs to know which individual to select. In this case, the user can provide an IndividualId or UserId to tell the stored procedure which individual to look up.
System Stored Procedures
SQL Server includes a large number of system stored procedures to assist in database administration tasks. Many of the tasks you can perform via SQL Server Management Studio can be done via a system stored procedure. For example, some of the things you can do with system stored procedures include:
- configure security accounts
- set up linked servers
- create a database maintenance plan
- create full text search catalogs
- configure replication
- set up scheduled jobs
- and much more.
No comments:
Post a Comment