Calling a stored proc with the recompile option

[P]

I would like to call my stored proc and invoke the WITH RECOMPILE
option. I can't figure out what to set in ADO to make that happen.
Anyone know how to make this work? BTW, I _don't_ want to create the
stored proc using the WITH RECOMPILE.

[Bob Barrows]

P wrote:

I would like to call my stored proc and invoke the WITH RECOMPILE
option. I can't figure out what to set in ADO to make that happen.
Anyone know how to make this work? BTW, I _don't_ want to create the
stored proc using the WITH RECOMPILE.

Two options:
1. Use the WITH RECOMPILE option in the string you use to execute the stored
procedure:
sSQL = "EXECUTE MyProc 'test' WITH RECOMPILE"
cn.Execute sSQL,,adCmdText

2. Create a stored procedure which executes the procedure you wish to
recompile before execution:
in SQL Server:
CREATE PROCEDURE ExecMyProc
@parm varchar(10) AS
EXECUTE MyProc @parm WITH RECOMPILE

in ASP:
cn.ExecMyProc @parm
HTH,
Bob Barrows

--
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.

[Bob Barrows]

in ASP:
cn.ExecMyProc @parm

should be:
cn.ExecMyProc 'test'

[Keith]

Nevermind, Bob's answer is better.

-----Original Message-----
I would like to call my stored proc and invoke the WITH RECOMPILEoption. I can't figure out what to set in ADO to make that happen.Anyone know how to make this work? BTW, I _don't_ want to create thestored proc using the WITH RECOMPILE.
.