Pass That Parameter Over Here! - Creating the stored procedure
(Page 2 of 4 )
As mentioned above, we will be using ASP and ADO to access SQL Server 2000 and execute a stored procedure using parameters. Lets start by creating our own stored procedure. To save development time, we will create our stored procedure to work with the “Pubs” database that comes as part of every standard SQL Server 2000 installation.
Jump over to your database server, fire up Query Analyser (Start -> Programs -> Microsoft SQL Server -> Query Analyser) and login to your database with administrative rights. If you haven’t changed any settings, the default username of “sa” with no password should be fine.
Our stored procedure will interface with the authors table of the pubs database. It will return the number of authors who live in a specific state. Our input parameter (stateName) will hold the value of the state that we want to search on. Our output parameter (numResults) will return the number of authors that live in the state that we have specified. Enter the following code into the Query Analyser window (Ignoring the line numbers):
1. USE Pubs
2. GO
3.
4. CREATE PROCEDURE sp_GetAuthorCountByState
5. @stateName [CHAR](2),
6. @numResults [INT] OUTPUT
7.
8. AS
9. SELECT @numResults =
10. ( SELECT COUNT(*)
11. FROM authors
12. WHERE state = @stateName
13. )
14. GO
For those of you who are just starting to learn SQL, or haven’t worked with parameterised stored procedures before, the code above may look a little challenging. As usual, I’ll walk you through the code in a line-by-line format.
On line one, we’re letting SQL server know that all of the commands we’re about to issue should be executed against the Pubs database. If we left line one out, all of our commands would be executed against the master database (not a good idea). The “GO” command on line two tells SQL Server to “execute the batch of commands up until this line but don’t go any further”, meaning that only our “USE Pubs” command would be executed (Switching the selected database from master to pubs).
Lines four handles the declaration of our stored procedure. Our stored procedure will be named sp_GetAuthorCountByState. When you’re creating stored procedures it’s a good idea to prefix them with “sp_” or something similar because:
1. They can be easily identified as stored procedures
2. It helps maintain good coding habits.
On line 5 we declare out input parameter. We’re telling SQL server that we will be passing it a variable named stateName. StateName will be of type character and will be two bytes long.
On line 6 we declare our output parameter that will be used to store the number of results matching our query. The parameter will be named numResults and will be of type integer. Notice the special “OUTPUT” declaration tacked onto the end? This just lets SQL Server know that the numResults variable needs to be returned to whichever program/script called the stored procedure.
Line eight starts the body of our stored procedure and lines nine to thirteen use the SQL COUNT() function to set the output parameter (numResults) to the number of rows in the authors table whose state field matches the value of our input parameter (stateName). There are two important points to note here:
1. All variables are prefixed with the @ symbol
2. When the value of a variable is changed, we use the SELECT command to set the new value.
To save our stored procedure under the Pubs database, press F5 or click the play button on the toolbar. Query Analyser should respond with “The command(s) completed successfully.”, meaning that our stored procedure was created without any trouble. You can make sure your stored procedure was correctly registered by using Enterprise Manager. Drill down to the Stored Procedures node of the Pubs database and you should see something like this:
Now that we’ve created our stored procedure, we can use ASP and ADO to execute it.
Next: Executing our stored procedure >>
More ASP Articles
More By Mitchell Harper
