Re: most efficient way to use a stored proc within a loop?
- From: MSUTech <MSUTech@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 27 Dec 2007 13:09:30 -0800
thanks!
"Bob Barrows [MVP]" wrote:
MSUTech wrote:.
Hello All,
What is the most efficient way to re-use paramaters within a loop?
Create and append the parameter objects BEFORE the loop (without setting
the values for the parameters whose values need to change in the loop).
Set the parameter values that depend on the loop cycle within the loop.
Probably the most efficient way is to instantiate object variables
referencing the parameter objects whose values need to change within the
loop.
set cmd=createobject("adodb.command")
dim parm1,parm2, ..., parmN
set parm1=cmd.createparameter("firstparm",...)
cmd.parameters.append parm1
set parm2=cmd.createparameter("secondparm",...)
cmd.parameters.append parm2
....
for ...
parm1.value = ...
parm2.value = ...
cmd.execute
next
If you are not reading the Return parameter value, and you are not using
output parameters, then you do not even need to use a Command object.
The technique I use most often is the
"procedure-as-connection-method" technique. With ADO 2.5 and higher,
stored procedures can be called as if they were native methods of the
connection object, like this:
conn.MyProcedure parmval1,...,parmvalN
This completely avoids the need to worry about delimiters, literal or
otherwise. Plus it turns out that this technique also causes the
procedure to be executed in a very efficient manner on the SQL Server
box.
You can also use this technique if your procedure returns a recordset:
set rs=server.createobject("adodb.recordset")
'optionally, set the cursor location and type properties
conn.MyProcedure parmval1,...,parmvalN, rs
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
- References:
- Re: most efficient way to use a stored proc within a loop?
- From: Bob Barrows [MVP]
- Re: most efficient way to use a stored proc within a loop?
- Prev by Date: Re: most efficient way to use a stored proc within a loop?
- Previous by thread: Re: most efficient way to use a stored proc within a loop?
- Index(es):
Relevant Pages
|
|
