Dynamic or Static SQL exact definition...

Hi,
Is ther any body who kindly explain me the exact meaning of Dynamic and Static SQL please? I tought know this but it seems I got it wrong!
some EXAMPLES would be so appreciated:)
-Best regardsThe difference between dynamic and static SQL lies in how the SQL statement itself is generated. A statement that is always submitted the same way, is fully static. A statement that allows parameter substitution is considered static, even though there will be small changes in each usage (due to changes in the parameters). A statement that is composed "on the fly" is considered fully dynamic, meaning that the SQL engine will get wildly varying SQL statements each time the dynamic SQL is used.

Some examples using Perl would be:$fooID = "'bar'"; # just a SQL constant
$col_list = "*"; # could be any list of columns
$table = "foo"; # table name
$where = "where 0 = 1"; # returns no rows at all

$static = "SELECT * FROM foo";
$parm = "SELECT * FROM foo WHERE fooID = $fooID";
$dynamic = "SELECT $col_list FROM $table $where";-PatP|||My, how different the worlds that we live in are !

I am sure that Pat's definition is correct in a context that is unknown to me, but in the Sybase world:
"Dynamic SQL" is what happens when you send a batch of SQL to the server. The server parses it, builds a query plan (identifies and resolves referenced objects) for it, builds a query tree (identifies resources) for it, allocates resources for it, executes it, and sends back the result set.

"Static SQL" is what happens when you put the [possibly same] SQL into a stored procedure and compile it (once). The server parses it, builds a query plan for it, builds a query tree for it (once). Then every time you execute the stored proc, it grabs the query plan (it may already be in memory for another user), allocates resources, executes it and returns the result set. Much faster because the required objects are known, parsing is not required, etc, and yes, changes to the objects referenced in the stored proc force it to be recompiled. Dynamic VALUES (known only at runtime) are passed as parameters to the static query, and this is quite ordinary. Dynamic SQL also has various limitations (eg. Exec(); no of verbs; error checking and passing back, to name just a few) which Static SQL does not have, but most important, it is hideous re transaction control.

One characteristic of a 'poor' application is that is stores the SQL in the client-side program, and therefore is always running Dynamic SQL, with the above overheads. Further, such SQL may not always work (eg. due to values out-of-range, programming errors, etc). In this regard a 'good' application has all its SQL stored on the server (well once that particular release of the app has been developed, the SQL does not change), and the SQL always works. Server objects can be monitored and administered with a lot more ease than trying to catch a bunch of SQL executing on the fly.

Some would say that SELECTs do not matter: to an extent that is true, but 'really good' apps (in this one regard) compile everything (why would you want to run at half the speed ?!?). (Of course, third party reporting tools cannot help but do Dynamic SELECTs).

BTW (and I am not addressing what is clearly an example above) anyone actually implementing SELECT * FROM <table> or INSERT ... without a column list should change careers. Email me if you do not know why.|||derek, nice exposition

however, i take issue with a couple of your points

you say "Much faster ..." and also "why would you want to run at half the speed ?!?)"

please provide benchmark figures to back up these egregiously inflated claims

if i've told you once, i've told you a million times, don't exaggerate

email me if you don't know why this is unprofessional

:) :) :)sql

Dynamic field list

Hi,
The underlying query in the dataset of my report has a set number of static
fields (which I bind to report elements) but also can return additional
variable number of fields, depending on passed parameters. Is there a way to
access those fields at report runtime?
Thanks.How about creating a SQL View, which has your current Static Column &
Computed Column & then returning that as Query to your Report?
On May 2, 1:10=A0pm, "Yuriy Galanter" <y...@.galanter.net> wrote:
> Hi,
> The underlying query in the dataset =A0of my report has a set number of st=atic
> fields (which I bind to report elements) but also can return additional
> variable number of fields, depending on passed parameters. Is there a way =to
> access those fields at report runtime?
> Thanks.|||That's the thing - I don't know in advance *how many* dynamic columns I am
going to return. Let's say I pass no parameters - the query will return
columns:
A B C
If I pass parameter "1" the query will return columns
A B C D
If I pass parameter "2" the the query will return columns
A B C D E
field list in dataset in report definition can contain only static number of
fields and if I bind report to A B C then D and E become unaccessable even
if query returns them.
The only way I can think of is, since I am launching the report from a .NET
application anyway is download report definition and modify it on the fly by
adding new columns to dataset field list. But I'd like to avoid it if
possible.
<prabhupr@.gmail.com> wrote:
How about creating a SQL View, which has your current Static Column &
Computed Column & then returning that as Query to your Report?
On May 2, 1:10 pm, "Yuriy Galanter" <y...@.galanter.net> wrote:
> Hi,
> The underlying query in the dataset of my report has a set number of
> static
> fields (which I bind to report elements) but also can return additional
> variable number of fields, depending on passed parameters. Is there a way
> to
> access those fields at report runtime?
> Thanks.|||Not tested , Just an idea - Does the use of
=IIF(Fields!Column_1.IsMissing, true, false)in the hidden property of the
coloumn solve your problem ?
P.I.
"Yuriy Galanter" <yuri@.galanter.net> a écrit dans le message de news:
eXm6bCJrIHA.4848@.TK2MSFTNGP05.phx.gbl...
> Hi,
> The underlying query in the dataset of my report has a set number of
> static fields (which I bind to report elements) but also can return
> additional variable number of fields, depending on passed parameters. Is
> there a way to access those fields at report runtime?
> Thanks.
>

Dynamic and Static

Is it possible to have a static and dynamic filter in merge replication?
I've got a publication that goes out to about 80 subscribers. I'm using
HOST_NAME() to filter by subscriber and it works great. As we've moved on,
its been decided that we only need the past three months of data at the
subscriber. I tried to change the filter to something like:
WHERE fdLocation = HOST_NAME() AND fdTransDate > DATEADD(day, -78,
GETDATE())
But when I try to replicate, no data at all moves. Is something like this
possible?
Thanks,
Scott
are you using this on a pull subscription? if you are using it on a push
subscription host_name() evaluates to the Publisher or the computer you are
connected to EM on. You can get around this by using the -hostname parameter
in your agent properties.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Scott" <john@.doe.com> wrote in message
news:%23QmyctYTEHA.2372@.TK2MSFTNGP11.phx.gbl...
> Is it possible to have a static and dynamic filter in merge replication?
> I've got a publication that goes out to about 80 subscribers. I'm using
> HOST_NAME() to filter by subscriber and it works great. As we've moved
on,
> its been decided that we only need the past three months of data at the
> subscriber. I tried to change the filter to something like:
> WHERE fdLocation = HOST_NAME() AND fdTransDate > DATEADD(day, -78,
> GETDATE())
> But when I try to replicate, no data at all moves. Is something like this
> possible?
> Thanks,
> Scott
>
|||This is a pull subscription. To clarify, if I just use the HOST_NAME(),
things work fine. What I want to do is filter by HOST_NAME() and a date
range. Is this possible?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23ZDNfZcTEHA.1768@.TK2MSFTNGP10.phx.gbl...
> are you using this on a pull subscription? if you are using it on a push
> subscription host_name() evaluates to the Publisher or the computer you
are
> connected to EM on. You can get around this by using the -hostname
parameter[vbcol=seagreen]
> in your agent properties.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Scott" <john@.doe.com> wrote in message
> news:%23QmyctYTEHA.2372@.TK2MSFTNGP11.phx.gbl...
> on,
this
>
|||Scott, did you have any luck in finding a solution to your question.
I have a similar requirement ie. trying to use a merge dynamic filter to age off transactions more than 7 days old from subscriber databases
eg.
(isnull(datediff ( day , EVENT_DATE , getdate() ),0) <= 7)
and (SITE_NO in (select SITE_NO from SM_SITE where
SUBSCRIBER_LOGINNAME = suser_sname()))
The above works fine when creating the initial snapshot & dynamic snapshot however the merge agent does not then progressively age off the transactions.
Mark