| Throughout this chapter, we have discussed how to write SQL queries. One way to use SQL is to create a query in a string variable and then pass it to the database using ADO or some other data access technology. However, an even better method is to store the SQL queries in the database itself, and just call the stored query using ADO. Queries stored on a SQL server are called stored procedures and can be executed like any other SQL query. (In Microsoft Access, all queries are stored queries, because you have to create a new query object to even key in a SQL statement.) Stored queries act similarly to functions or subroutines in Visual Basic, and can include input and output parameters. Using stored queries presents several advantages over creating SQL from within your program: You can use the SQL statement more easily in multiple locations in your program or in multiple programs. Making changes to the SQL statement in a single location is easier, and does not require recompilation of the program. Because stored queries can be optimized and cached by the database management system, they run faster than those that are handled by parsing the statement from code every time. Creating multiple interfaces (Web, client/server, and so on) to the same data is easier because less database code has to be written.
In the later chapters on ADO, we will show you how to execute stored procedures from within Visual Basic. In this section, we will explain how to create them in your database management system.  For more on executing stored procedures, p.599
Creating a Simple Stored Procedure To create a stored procedure, you use the CREATE PROCEDURE statement in the following general format: CREATE PROCEDURE procedurename [parameterlist] AS SQL Query The following SQL statement creates a stored procedure in the database called spGetEmployeeBySSN: CREATE PROCEDURE spGetEmployeeBySSN @strSSN char(11) AS SELECT P.SSN, P.LastName, P.FirstName, E.Salary, E.Dept FROM Person P INNER JOIN Employee E ON P.SSN = E.SSN WHERE E.SSN = @strSSN As with variables and other functions, it is customary to follow a naming convention. The preceding sample stored procedure uses sp as a prefix to indicate it is a stored procedure, and the data type prefix str for the input parameter strSSN. The heart of the stored procedure is a SQL query, which is written just like any other SQL query. However, notice that the input parameters to the stored procedure can be used in the WHERE or SELECT clauses. Testing Your Stored Procedure To execute a stored procedure, you can simply type its name followed by values for all of its parameters, as in the following example: spGetEmployeeBySSN '111-22-3333' Notice the parameter is enclosed in quotes, because it is a character data type. Numeric parameters do not need to be enclosed in quotes. Note To execute multiple stored procedures within the same SQL session, or execute one stored procedure from within another, include the keyword exec before the stored procedure name.
Note SQL Server itself includes some built-in stored procedures, such as sp_help and sp_helptext, which display information about a specified table or stored procedure, respectively.
Note You can execute stored procedures on other SQL servers on the network, provided you have linked the servers together. This can be a quick way to import data from another server's table into your own, as in the following example: INSERT INTO MyTable EXEC YourServer.YourDB..spGetEmployee_ '111-22-3333' For more information, see the SQL Server Help topic "Linked Servers."
If you need to delete a stored procedure from the database, use the DROP PROCEDURE statement, which accepts the procedure name as a parameter. | 
