Calling a stored proc with the recompile option

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
Thu Nov 20 16:20:46 CST 2003

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
Thu Nov 20 16:24:57 CST 2003


> in ASP:
> cn.ExecMyProc @parm

should be:
cn.ExecMyProc 'test'

Keith
Thu Nov 20 16:36:39 CST 2003

Nevermind, Bob's answer is better.

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