Calling a stored proc with the recompile option

P

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

Bob Barrows

P said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top