sp_executesql under vb call

sp_executesql under vb call

Post by Andrea MOR » Mon, 18 Nov 2002 01:02:34



Reading the book on line, I see that using sp_executesql
procedure can apport a lot of benefit to sql server, but
also to application that can receive that more faster rather
than execute (and rebuild) every time the comple sql
statement.

I'd like to implement the execution of sp_executesql under
vb, but question now is "How implement it?".

I suppose better (and probably only) way is to use
the command object and pass to recordset a command
object and its commandtext properties.

Is there on the net some samples, or hve you got one?

Bye
Andrea

 
 
 

sp_executesql under vb call

Post by BP Margoli » Mon, 18 Nov 2002 05:48:00


Andrea,

It sounds to me like you are not routinely using stored procedures to
execute code, but rather sending SQL code directly to SQL Server. Stored
procedures are, except in very rare cases, the way to go. If you routinely
use stored procedures, then you automatically get the benefits of
sp_ExecuteSQL, because you have already parameterized your queries and the
query plans for stored procedures are cached.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Reading the book on line, I see that using sp_executesql
> procedure can apport a lot of benefit to sql server, but
> also to application that can receive that more faster rather
> than execute (and rebuild) every time the comple sql
> statement.

> I'd like to implement the execution of sp_executesql under
> vb, but question now is "How implement it?".

> I suppose better (and probably only) way is to use
> the command object and pass to recordset a command
> object and its commandtext properties.

> Is there on the net some samples, or hve you got one?

> Bye
> Andrea


 
 
 

sp_executesql under vb call

Post by Andrea MOR » Mon, 18 Nov 2002 20:54:39


I don't use always the stored procedure.
Just for insert, delete and update operation.

For select, because I'm just developing app,
I send sql command via recordset object
without any stored p.
This, also because, i developed my app in
dll modules, inserting in each module only
the sql code for the module.

In this way, when I need to perform updare,
just update dll and all is done.

To solve my "problem" now, i thinked to
develop an entry sp, that accept three
arguments. Sql string, sql params definition,
and sql params value.

In this way i can continue to have my private
dll, and update when needed, and when call
sql, i call it throught this entry sp that inside
merge variabile with dynamic sql, taking
benefit from the sp_executesql.

What do y think about?

Bye
Andrea

 
 
 

sp_executesql under vb call

Post by BP Margoli » Tue, 19 Nov 2002 08:38:19


Andrea,

My personal preference is to used stored procedures for not only INSERT,
DELETE and UPDATE operations, but for SELECTs as well. One advantage of the
approach, aside from more likely having cached query plans, is that other
people don't need to look in two places to see all the SQL code. I just
prefer having all the SQL code reside with the database ... just seems, at
least to me, to be the "natural" place for it.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> I don't use always the stored procedure.
> Just for insert, delete and update operation.

> For select, because I'm just developing app,
> I send sql command via recordset object
> without any stored p.
> This, also because, i developed my app in
> dll modules, inserting in each module only
> the sql code for the module.

> In this way, when I need to perform updare,
> just update dll and all is done.

> To solve my "problem" now, i thinked to
> develop an entry sp, that accept three
> arguments. Sql string, sql params definition,
> and sql params value.

> In this way i can continue to have my private
> dll, and update when needed, and when call
> sql, i call it throught this entry sp that inside
> merge variabile with dynamic sql, taking
> benefit from the sp_executesql.

> What do y think about?

> Bye
> Andrea