Dynamic query

Hello

I'm a newbie in sql server. anyone can explain what does Dynamic query means?

thanks.

Sure, but for some basic understandings (perhaps your questions will be cleared after the detailed the explanation of this text) look for:

http://www.sommarskog.se/dynamic_sql.html

HTH, jens Suessmeyer.

|||On Sat, 21 Jan 2006 11:30:01 -0800,

wrote:

>I'm a newbie in sql server. anyone can explain what does Dynamic query

>means?

Hi Dato0011,

Dynamic SQL means that the query is not precoded in a stored procedure

or application, but created on the spot. Example of non-dynamic SQL:

SELECT COUNT(*) FROM pubs..authors

Example of dynamic SQL:

DECLARE @.SQL nvarchar(400)

SET @.SQL = 'SELECT COUNT(*) FROM pubs..authors'

EXECUTE (@.SQL)

(In a real situation, part of the dynamic SQL would be based on user

input).

Dynamic SQL can be advantegeous in some situations, but it is also

extremely dangerous. Using dynamic SQL requires permissions for the end

users on your tables, and -the most important danger!- exposes your DB

to the danger of SQL injection.

For a completediscussion of the curse and blessing of dynamic SQL, see

Erland Sommarskog's article:

http://www.sommarskog.se/dynamic_sql.html

--

Hugo Kornelis, SQL Server MVP|||

Hi Dato0011,

It seems that some weird bug deleted the formatting of my reply. Let's do that one more time, shall we?

On Sat, 21 Jan 2006 11:30:01 -0800, <Dato0011@.discussions.microsoft.com>
wrote:

>I'm a newbie in sql server. anyone can explain what does Dynamic query
>means?

Hi Dato0011,

Dynamic SQL means that the query is not precoded in a stored procedure
or application, but created on the spot. Example of non-dynamic SQL:

SELECT COUNT(*) FROM pubs..authors

Example of dynamic SQL:

DECLARE @.SQL nvarchar(400)
SET @.SQL = 'SELECT COUNT(*) FROM pubs..authors'
EXECUTE (@.SQL)

(In a real situation, part of the dynamic SQL would be based on user
input).

Dynamic SQL can be advantegeous in some situations, but it is also
extremely dangerous. Using dynamic SQL requires permissions for the end
users on your tables, and -the most important danger!- exposes your DB
to the danger of SQL injection.

For a completediscussion of the curse and blessing of dynamic SQL, see
Erland Sommarskog's article:
http://www.sommarskog.se/dynamic_sql.html

--
Hugo Kornelis, SQL Server MVP

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 matrix reports

Hello,
Is it possible to create a report which allows users to select the
fields
for the matrix in a report. To explain in detail, can we allow the
users to
select the X and Y axis for a matrix in a report?
For ex., there is a report containing a matrix which shows the total
sales(
data cell) by month (X axis) and by Rep( Y axis). Can we have some
option
so that if the users select Year as the X axis and Company as the Y
axis
then they can view the same report but by company and Year instead of
Month and Rep?
Regards
Jaideepcertainly.
Just use a switch statement or iif statement for the values for the row
and column groups:
switch(parameters!group.value = 'x', fields!month.value,
parameters!group.value = 'y', fields!year.value)
or
iif(parameters!group.value = 'x', fields!month.value,
fields!year.value)
jai wrote:
> Hello,
> Is it possible to create a report which allows users to select the
> fields
> for the matrix in a report. To explain in detail, can we allow the
> users to
> select the X and Y axis for a matrix in a report?
>
> For ex., there is a report containing a matrix which shows the total
> sales(
> data cell) by month (X axis) and by Rep( Y axis). Can we have some
> option
> so that if the users select Year as the X axis and Company as the Y
> axis
> then they can view the same report but by company and Year instead of
> Month and Rep?
>
> Regards
> Jaideep