Dynamic Execute Statement vs. Passing Dynamic SQL
| Author |
Message |
|
Randy E. Jackso
#1 / 4
|
 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 |
|
 |
|
Tibor Karasz
#2 / 4
|
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 |
|
|
|
Randy E. Jackso
#3 / 4
|
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 |
|
|
|
Tibor Karasz
#4 / 4
|
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 |
|
| |
|
