RSS - Read full length articles at Stardeveloper using RSS

Overview

In this tutorial we are going to talk about yet another powerful feature of database programming, Stored Queries. Most of the time we use ASP pages, we use them to retrieve or manipulate records in the database. These database connections are resource heavy and every query takes time on part of the server. Thus we should try to make use of everything possible to optimize and speedup database access as much as possible. One of the ways is to retrieve records as arrays which I discussed in my article ' Speeding up Database Access' earlier. Now we are going to talk about Stored Procedures which provide another way of optimizing database access.

Stored Queries
They are actually SQL statements which are compiled and stored in the database. Normally we manually provide SQL statement every time we want to manipulate records in the database. e.g,

	Dim con
		Set con = Server.CreateObject("ADODB.Connection")

	' executing query
	con.Execute "insert into Table(col1, col2) values(val1, val2);"

	con.Close
	Set con = Nothing

In above code an Insert statement was executed. No matter how small and simple an SQL statement is, it takes times to be ported to the database server over the network where it is interpreted and then response generated.

Why use Stored Queries ?
Stored Queries are not textual SQL statements, rather they are compiled and stored in the database you are using just like Stored Procedures in SQL Server. They provide enormous speed improvements ( especially under stress ) as they neither need to be ported to the database server nor interpreted.

In an N-Tier environment they also help separate business layer from the data layer.

Example Code :
Code I provided earlier on this page has been edited to accomodate an Insert Stored Procedure in place of an Insert statement.

	Dim con
		Set con = Server.CreateObject("ADODB.Connection")

	' executing query
	con.Execute "exec InsertProc val1, val2"

	con.Close
	Set con = Nothing

Please notice that Stored Queries are equivalent to Stored Procedures in Microsoft SQL Server. The procedure of creating and running Stored Procedures in Microsoft SQL Server is different. In Access database, stored procedures are known as 'Queries'.

Access Database :
Start Microsoft Access and create a new blank database and save it as "StoredProc.mdb". Now create a new table in 'design view' and save this table as 'Names'. It contains only two fields and should look like following :

You should have 'Names' listed in your tables list now, as shown below :

Now click Queries under the Objects tab on the left. Now double click 'Create query in Design View'.

Two windows will open. Close the 'Show Tables' window by clicking the close button.

Now right click in the 'Query1 : Select Query' window and click 'SQL View' option as shown below.

You should get a wide white window with something like following written on it's top left.

Our first Stored Query
We are now ready to write down our first stored query. This will be a simple insert SQL statement which will require single value ( Name ) to be inserted into our Names table.

Now copy the following SQL statement and paste it into the 'Query1 : Select Query' window :

	INSERT INTO [Names] (Name) VALUES ([@newName]);

Explanation
Above is a simple Insert SQL statement like the one you use in ASP pages. The only difference is that we don't give it any value except a variable name ( @newName ) which will be our only argument to this stored query when we go to run it. This argument will be any value we want inserted in the database.

Now close the 'Query1 : Select Query' window. A small window will pop asking if you want to save changes to the design of query 'Query1'.

Click 'Yes'. A 'Save As' window will pop up asking you to give name to this Query. Give it InsertProc. Note this can be any name, I've given it this name so as to be a shortcut for 'Insert Procedure'. Many developers like appending 'sq_' before the stored query name e.g, in our case the stored procedure ( Query ) will become 'sq_InsertProc'. As far as I am concerned I don't care what name you give to it, just make sure it is easily rememberable, ;)

You should now have InsertProc Query listed under 'Queries' tab.

Now lets try this one out. We can do this even without running it in an ASP page ( we will but later ). Double click the listed InsertProc icon. A warning window will pop up ( see below ) telling you that you are about to modify data in your table, click 'Yes'.

Now you will see a small 'Enter Parameter Value' window with our variable @newName written over the input box. Enter any name and hit 'OK'.

Another warning window will open telling you that you are about to append one row, click 'Yes'.

Now to check if record has been entered or not go to 'Tables' window and double click 'Names' table. You should see your entered name in there.