Using Stored Procedures
Some of the most important concerns to a database user are speed and efficiency. Faster data retrieval provides applications to responds and behaves quickly. SQL Server provides “Stored Procedures” for faster and quick data access.
Stored Procedures is a query that is stored in a database on SQL Server rather than being stored in the front-end code on users machine. You may think that storing queries in the form of Stored Procedures will be an overhead itself??? But its not the case here. Stored Procedures are of three types.
- User-defined Stored Procedures
- System Stored Procedures
- Extended Stored Procedures
We will mainly focus on User-defined stored procedures, although we will also take a look on System and Extended Stored Procedures.
Stored Procedures provide two major benefits over ad-hoc queries.
- Better Performance
- Database Management.
Better Performance:
As Stored Procedures resides on the server, they remarkably enhance its performance and release the network traffic from congestion.
Consider a database “TestDB” with a table, named “Customer”. It has three columns FirstName , LastName and Location. We are going to execute our queries on this table.
We are writing an ad-hoc query, which selects the First Name and Last Name of all customers from “Customer” table, which lives in “New York”. The query looks
SELECT FirstName , LastName
FROM Customer
WHERE Location = ‘New York’
ORDER BY FirstName
Although, the query doesn’t seems to be large, imagine having 1000 users which are executing this query many times in a day, sending a large traffic over the network and causing congestion (as the query resides on client machine, which send it to server for execution).
Every time the whole query sends to the server (in text format) and receiving the result. Instead of doing this, we should put our query on the server (in the form of Stored Procedure) and just call to execute it and return the recordset. The only could then send over the network (from the client machine) is
EXEC stored_procedure_name
Another advantage of Stored procedures is that they are precompiled. Whenever you send an ad-hoc query to SQL Server, it reads the query, looks at such things as JOINS, WHERE clauses etc. Then it creates the execution plan in memory. Where as Stored Procedures are compiled one time and they just need to be executed. The have gone through all the above process and have a plan waiting in memory, so they execute faster.
Database Management:
Next advantage of stored procedures is they make database management process easier. Suppose you want to change an ad-hoc query and the query resides on the client machine then what will you do??? You will change the query in all the client machines where as in Stored Procedures you just make the changes on the server (as they reside on the database server) and that’s all.
Remember that using stored procedures are not always suitable. If your query doesn’t not run frequently then you can use ad-hoc queries.
Creating Basic Stored Procedure:
We are going to write the above ad-hoc query (which we have gone through above) in the form of Stored Procedure. Steps are
- Open Enterprise Manager, expand your database server, then your selected database (on which you will write Stored Procedure)
- Under “TestDB” database, select “Stored Procedure”
- From the action menu, select “New Stored Procedure”
- A pop-up window comes. Write the code in it.
CREATE PROCEDURE Show_Customer AS
SELECT FirstName, LastName FROM Customer
WHERE Location=’New York’
ORDER BY FirstName
- Click the “Check Syntax” button to check whether the syntax is correct or not.
- Press the “Apply” or “Ok” button.
To execute the procedure,
- Open Query Analyzer
- Enter the following code in it
USE TestDB
EXEC Show_Customer - Click the Execute button
Now, We have understood how to create and execute Stored Procedures. But here in our Stored Procedure, there is a problem i.e. if we want to view customers having different locations then it will not help us as it can return only customers live in New York. Here comes Stored Procedures with Input Parameters.
[catlist id=182].