Dynamic Execute Statement vs. Passing Dynamic SQL 
Author Message
 Dynamic Execute Statement vs. Passing Dynamic SQL

I have a client app (VB) that builds dynamic SQL statements, passes
them to the server (6.5) and process the result set.

I wanted to replace this by creating stored procedures and passing
parameters to them.  Unfortunately, the SQL statements us the IN
clause:

SELECT field1, field2, field3
WHERE ProdCode IN (x1, x2, x3)

I was told in replies to a previous post that I could not create a
parameter variable in the store procedure and pass in the  '(x1, x2,
x3)' part of the statement with the IN clause.  I was told that in
order to do this with a stored procedure I would need to build the
string and execute it using the EXECUTE statement.

This seems just as dynamic as what I am currently doing, in does
"clean up" the client side code a bit, but it seems like essentailly
the same thing, I can't see where it really buys me anything, because
now I am dynamically building the string on the server.

Comments? Suggestions?



Sun, 17 Feb 2002 03:00:00 GMT
 Dynamic Execute Statement vs. Passing Dynamic SQL

Randy,

It depends on the surroundings, a bit, IMHO.
You could still gain by:

Not submitting large strings to the server (you submit shorter proc-names,
and the parameters).
Having other stuff in the proc which is _not_ executed dynamically.

--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.



Quote:
> I have a client app (VB) that builds dynamic SQL statements, passes
> them to the server (6.5) and process the result set.

> I wanted to replace this by creating stored procedures and passing
> parameters to them.  Unfortunately, the SQL statements us the IN
> clause:

> SELECT field1, field2, field3
> WHERE ProdCode IN (x1, x2, x3)

> I was told in replies to a previous post that I could not create a
> parameter variable in the store procedure and pass in the  '(x1, x2,
> x3)' part of the statement with the IN clause.  I was told that in
> order to do this with a stored procedure I would need to build the
> string and execute it using the EXECUTE statement.

> This seems just as dynamic as what I am currently doing, in does
> "clean up" the client side code a bit, but it seems like essentailly
> the same thing, I can't see where it really buys me anything, because
> now I am dynamically building the string on the server.

> Comments? Suggestions?



Sun, 17 Feb 2002 03:00:00 GMT
 Dynamic Execute Statement vs. Passing Dynamic SQL
On Wed, 1 Sep 1999 17:28:57 +0200, "Tibor Karaszi"

Quote:

>Randy,

>It depends on the surroundings, a bit, IMHO.
>You could still gain by:

>Not submitting large strings to the server (you submit shorter proc-names,
>and the parameters).
>Having other stuff in the proc which is _not_ executed dynamically.

In the orignal statement:

SELECT field1, field2, field3
WHERE ProdCode IN (x1, x2, x3)

...An idea, is it possible to replace the '(x1, x2, x3)' in the
statement with a sub query or stored procedure (main sp calls another
sp) that returns x1,x2, and x3 and have the stored procedure compile,
and not be forced to use the EXECUTE statement?  That way I can get
the full benefit of using a stored procedure.



Sun, 17 Feb 2002 03:00:00 GMT
 Dynamic Execute Statement vs. Passing Dynamic SQL
I very much doubt that you would gain anything by introducing a subquery
instead of using constants. But you could give it a try and look at
statistics io, of course...

--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.



Quote:
> On Wed, 1 Sep 1999 17:28:57 +0200, "Tibor Karaszi"

> >Randy,

> >It depends on the surroundings, a bit, IMHO.
> >You could still gain by:

> >Not submitting large strings to the server (you submit shorter
proc-names,
> >and the parameters).
> >Having other stuff in the proc which is _not_ executed dynamically.

> In the orignal statement:

> SELECT field1, field2, field3
> WHERE ProdCode IN (x1, x2, x3)

> ...An idea, is it possible to replace the '(x1, x2, x3)' in the
> statement with a sub query or stored procedure (main sp calls another
> sp) that returns x1,x2, and x3 and have the stored procedure compile,
> and not be forced to use the EXECUTE statement?  That way I can get
> the full benefit of using a stored procedure.



Mon, 18 Feb 2002 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. HOW TO create cursor from dynamic SQL statement (EXECUTE(@Statement))

2. executing dynamic sql statements in a PL/SQL procedure

3. executing dynamic sql statements in a PL/SQL procedure

4. Passing Datetime to Dynamic SQL Statement

5. executing a long dynamic sql statement in a stored procedure

6. Executing a Dynamic SQL Statement in 6.5

7. Query with executing dynamic sql statements and returning result to variable

8. Access of temporary table with # prefix outside dynamic sql (execute statement)

9. Retrieving results from Dynamic SQL statements executed with Sp_ExecuteSql

10. OnLine Dynamic Server 7.2 Vs INFORMIX Dynamic Server 7.3

11. joined dynamic filters VS dynamic filters [mergerepl]

12. Executing dynamic select statement in a SP


 
Powered by phpBB® Forum Software