SQL Server Stored Procedures

Stored procedures are procedures that are stored in the SQL Server database. Their purpose is to enable you to take frequently used SQL statements and save them for reuse. You can then execute a stored procedure when you need it. A stored procedure is similar in concept to the VBA procedures and SQL statements you have written so far in this book. However, stored procedures (stored in the SQL Server database) are more efficient than passing SQL statements to the database on the fly because they are precompiled.

You can view stored procedures from the Queries node in the Database Window. In fact, the Queries node will display all stored procedures, views, and functions that are stored in the SQL Server database. Figure 9.15 illustrates several stored procedures, as well as one view called titleview.

When you open the byroyalty stored procedure in Figure 9.15, a designer window like the one shown in Figure 9.16 appears.

Figure 9.16

Notice how a table is displayed as well as a grid showing the columns to be output when the stored procedure executes. You can view the SQL code for the stored procedure by selecting View C> SQLView; a screen similar to the one shown Figure 9.17 will appear.

ALTER PROCEDURE dbo.byroyalty f^oeicentaae intj

AS SELECT aujd

FROM dbo.tjdeauthor

WHERE (royaltyper = ^percentage)

Figure 9.17

In the example of Figure 9.17, the stored procedure accepts a parameter called @percentage. When you call the stored procedure from VBA code, you must specify the parameter. You might call the stored procedure using the following code:

Dim cmdCommand As ADODB.Command Dim prmPercent As ADODB.Parameter

'Create a new command object

Set cmdCommand = New ADODB.Command cmdCommand.ActiveConnection = CurrentProject.ActiveConnection

'Specify the stored procedure to run cmdCommand.CommandType = adCmdStoredProc cmdCommand.CommandText = "byroyalty"

'Create the percentage parameter

Set prmPercent = cmdCommand.CreateParameter("@percentageM, adInteger, adParamInput)

prmPercent.Value = 100

cmdCommand.Parameters.Append prmPercent

'execute the Stored Procedure cmdCommand.Execute

In the preceding example, notice how the ADO Command object is used to specify that a stored procedure should be executed. The ADO Parameter object is used to specify the parameters that should be passed to the stored procedure. The Execute method of the Command object is then executed to run the stored procedure.

0 0

Post a comment