| Answer: |
Here are two good ways to run stored procedures that accept only input parameters or no parameters at all. The first is simple; call the Execute method of the connection object, passing the correct string to run the procedure. The first example calls a stored proc that takes one parameter. On one line of code it executes the procedure and also executes the GetString method on the recordset that the Execute method returns.
<%
Dim objConn, intParam
intParam = 2
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Open "NWind" , "Guest", "welcome"
' Execute, Getstring and Write all on one line:
Response.Write objConn.Execute("pGetEmployees " & intParam).GetString()
Set objConn = Nothing
%> |
Note that the above method will also work on Access 97/2000 parameter queries. Here is the SQL server stored procedure that this example calls (in the SQL Server Northwind sample database):
CREATE PROCEDURE pGetEmployees
@intNumber int = NULL
AS
IF (@intNumber IS NULL) OR (@intNumber < 1)
SELECT * FROM Employees
ELSE
SELECT * FROM Employees WHERE EmployeeID = @intNumber |
The second method seems to be lesser known, but is very intuitive. You can call SQL Server and Access 97/2000 procedures and queries, respectively, as if they were methods of the connection object (I haven't had a chance to test this in Oracle, but it may also work in that database). Here is the example of that method:
<%
Dim objConn, intParam
intParam = 2
Set objConn = Server.CreateObject("ADODB.Connection")
' You must create a recordset to pass to the call, if you
' are returning rows from the database:
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Open "NWind" , "Guest", "welcome"
' Pass any input parameters first, then the
' recordset object to populate with data
objConn.pGetEmployees intParam, objRS
' Do something with the recordset here
Set objRS = Nothing
Set objConn = Nothing
%> |
For more info on stored procedures see these links:
Parameterized Queries in Access
Writing a Stored Procedure
Using Dynamic SQL Statements in Stored Procedures
Happy Programming!
|
