Re: Command object and batching stored procedure calls

Michael Viking wrote:
Greetings,
I've tried searching online for what I think must be easy to do, but
so far no luck. I have to call a stored procedure over and over as
data comes in. I'd like to queue the calls up on the client side and
send them in one batch to avoid all the network traffic but I can't
figure out how.

That's because there is no way without using dynamic sql to concatenate
all the procedure calls into a single string

sql="exec proc 'parm1';...;exec proc 'parmN'"
cmd.commandtype=adCmdText
cmd.commandtext = sql
cmd.execute ,,adExecuteNoRecords

Of course, doing so loses the value provided by using parameters:
security, strong datatyping, etc. Also, not all dbrms support batched
calls.

I'm not sure what you think you would be gaining by batching the
statements up. Session pooling should guarantee connection re-use if you
are worried about spawning too many connections.



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


.

Relevant Pages

  • Re: How do I link the results from sp_executeSQL to a gridview?
    ... I built a stored procedure that needed to use dynamic sql for a search ... but this is a classic ADO newsgroup. ... gridview and simply call the Fillmethod but the ...
    (microsoft.public.data.ado)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... stored procedure, the user needs EXECUTE rights to the stored procedure. ... Query 1 requires the user to have SELECT rights on the Customers ... Also, FWIW, the dynamic SQL is only syntax checked at run time, not when the ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Injection Prevention
    ... here is the summary from my posts to that thread that are related to your ... introducing another SQL injection vulnerability. ... of triggers that are using dynamic sql). ... dynamic SQL in stored procedure is much more likely to occur than ...
    (microsoft.public.sqlserver.server)
  • Re: Pass-thru SQL performance vs Stored Proc
    ... >> are going to have to create a lot of dynamic SQL based on user selects ... > stored procedure per permutation of parameters, ... > ms per execution, and you are executing it 100 times an hour, no problem, a ... Right now all our code is in stored procs. ...
    (microsoft.public.sqlserver.programming)
  • Re: stored procedure security
    ... > execute privelages on the stored procedure, ... As soon as you employ dynamic sql, the logon executing the stored procedure ... In order to execute this stored ...
    (microsoft.public.sqlserver.security)
Loading