Part VI: Access as an Enterprise Platform
1220
l (^) They’re stored in a semi-compiled, interpretive state on the database server, so they exe-
cute faster than if they were embedded in your code. In other words, stored procedures
are typically not compiled into a relational database as binary code, but they’re usually
pre-parsed, and partially pre-executed, making for faster execution.
l (^) They’re stored in a common container in your application so that others can maintain
them more easily because there is less database access code.
l (^) After a stored procedure has been added to a SQL Server database, it’s accessible to any
client application using that database. This means that an Access desktop database appli-
cation will execute the exact same logic as a Web application written with Visual Studio
.NET, if they both use the same stored procedure to access data.
Here are some of the disadvantages of stored procedures:
l Overuse of stored procedures tends to place too much business logic into a database. This
can sometimes make number-crunching-type business logic execute in a database very
slowly. Some types of processing are best left to application coding, which is often much
better suited to intense calculations.
l Overuse of stored procedures for data access can sometimes cause serious issues with net-
work performance.
SQL Server stored procedures are usually executed through ADO code. Here is a small example of
calling a SQL Server stored procedure:
Public Sub StoredProcTest()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
sConnect= “driver={sql server};”
& “server=DAD-PC\SQLEXPRESS;”
& “Database=Northwind;UID=;PWD=;”
‘ Establish connection.
Set cnn = New ADODB.Connection
cnn.ConnectionString = sConnect
cnn.Open
‘ Open recordset.
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = “sp_MyProc”
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1).Value = 10
Set rs = cmd.Execute()
‘Use the recordset’s data here...
‘ Process results from recordset, then close it.
rs.Close
Set rs = Nothing
End Sub