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 columns

Hi all, a quicky.

What is wrong with:

CREATE PROCEDURE dbo.QuotePrice
@.DateFrom datetime,
@.DateTo datetime
AS
declare @.days as int
declare @.price as smallint
declare @.daycolumn as nvarchar(6)

set @.price = 0

set @.days = DATEDIFF(day, @.DateFrom, @.DateTo)

set @.daycolumn = 'Day_' + CAST(@.days AS nvarchar(2))

set @.price = (SELECT @.daycolumn FROM pasPriceTable)

return @.price
GO

Problem is:
Syntax error converting the nvarchar value 'Day_10' to a column of data type smallint

How do I set a variable to a result from a query?
Thanks,
/ j0rgeYou can't use a variable to reference a column name...
You need to build a string and then eval the string... see below.

BTW, you'll want to put a where clause in to be sure you return a single value for price..

You should look into using sp_executesql as well...

Hope this helps...
jzapp
--------------------
CREATE PROCEDURE dbo.QuotePrice
@.DateFrom datetime,
@.DateTo datetime
AS
declare @.days as int

declare @.daycolumn as nvarchar(6)

declare @.str varchar(500)

set @.days = DATEDIFF(day, @.DateFrom, @.DateTo)

set @.daycolumn = 'Day_' + CAST(@.days AS nvarchar(2))

SET @.str = '
declare @.price as smallint
set @.price = 0
set @.price = (SELECT ' + @.daycolumn + ' FROM pasPriceTable WHERE ID =1)
return @.price
'
EXEC(@.str)

GO|||You can't use a variable to reference a column name...
YES YOU CAN. This works:

CREATE PROCEDURE dbo.pasQuotePrice
@.DateFrom datetime,
@.DateTo datetime
AS
declare @.days as int
declare @.price as smallint
declare @.daycolumn as nvarchar(6)

set @.price = 0

set @.days = DATEDIFF(day, @.DateFrom, @.DateTo)

set @.daycolumn = 'Day_' + CAST(@.days AS nvarchar(2))

SELECT @.daycolumn FROM pasPriceTable
GO

BUT the result gets returned as a record not as a value with return which is what I need.
/ jorge

Originally posted by jzapp
You can't use a variable to reference a column name...
You need to build a string and then eval the string... see below.

BTW, you'll want to put a where clause in to be sure you return a single value for price..

You should look into using sp_executesql as well...

Hope this helps...
jzapp
--------------------
CREATE PROCEDURE dbo.QuotePrice
@.DateFrom datetime,
@.DateTo datetime
AS
declare @.days as int

declare @.daycolumn as nvarchar(6)

declare @.str varchar(500)

set @.days = DATEDIFF(day, @.DateFrom, @.DateTo)

set @.daycolumn = 'Day_' + CAST(@.days AS nvarchar(2))

SET @.str = '
declare @.price as smallint
set @.price = 0
set @.price = (SELECT ' + @.daycolumn + ' FROM pasPriceTable WHERE ID =1)
return @.price
'
EXEC(@.str)

GO|||When you select a variable such as...
SELECT @.colname FROM table, what you get is the actual string value of that variable for every record in the table... and not the data value of the field.
It's equivalent to...
SELECT 'Day_10' FROM table (SQL interprets Day_10 as a string and not as a fieldname)
Your recordset is probably...
Day_10
Day_10
Day_10
.
.
.

Also, as I stated before, unless you know that this query will only return one record, you will need to put in a WHERE clause..

regards...|||Weird, i swar it worked somehow before.
Thanks.
/ jorge

Originally posted by jzapp
When you select a variable such as...
SELECT @.colname FROM table, what you get is the actual string value of that variable for every record in the table... and not the data value of the field.
It's equivalent to...
SELECT 'Day_10' FROM table (SQL interprets Day_10 as a string and not as a fieldname)
Your recordset is probably...
Day_10
Day_10
Day_10
.
.
.

Also, as I stated before, unless you know that this query will only return one record, you will need to put in a WHERE clause..

regards...|||Oh no, trouble running the EXEC version.
A RETURN statement with a return value cannot be used in this context.
/ jorge

Originally posted by j0rge
Weird, i swar it worked somehow before.
Thanks.
/ jorge|||rats...

How about
SELECT @.price AS Price|||Nope.. same thing.

See the problem is I need a return value and returns cannot be a part of a EXEC statement.

BUT if there is a @.@. command to get the last run query then I could:

set @.price = (select price from @.@.lastrunstatementrowone)
return @.price

I've battled with this for a while now.

Maybe there is another way to make the table.
I have a specific amount of days which have one price for a certain period.

that is:
Valid from 12/2003 to 12/2004
Day1=20
Day2=25
...
Day40=240

There can be 'overrides' for specific dates and days.
Valid from 04/2004 to 05/2004
Day20=60
Day21=60

Any ideas? instead of running dynamic SQL?
/ jorge


Originally posted by jzapp
rats...

How about
SELECT @.price AS Price|||Just off the top of my head you could do something like this...
(I haven't tested this code)

SET @.str = '.....
set @.price = 0
set @.price = (SELECT ' + @.daycolumn + ' FROM pasPriceTable WHERE ID =1)

CREATE TABLE #temptable (
ColA [int] IDENTITY (1, 1) NOT NULL,
ColB varchar(50) NOT NULL
)

INSERT INTO #temptable (ColB) VALUES ( @.price )
'

EXEC(@.str)

declare @.p varchar(20)
SELECT @.p = ColB FROM #temptable WHERE ColA = @.@.identity
return @.p|||This code will run A LOT. I want it to be simple, and there must be a way of querying the 'last ran statement'. NO?

Thanks for you help.
/ jorge

Originally posted by jzapp
Just off the top of my head you could do something like this...
(I haven't tested this code)

SET @.str = '.....
set @.price = 0
set @.price = (SELECT ' + @.daycolumn + ' FROM pasPriceTable WHERE ID =1)

CREATE TABLE #temptable (
ColA [int] IDENTITY (1, 1) NOT NULL,
ColB varchar(50) NOT NULL
)

INSERT INTO #temptable (ColB) VALUES ( @.price )
'

EXEC(@.str)

declare @.p varchar(20)
SELECT @.p = ColB FROM #temptable WHERE ColA = @.@.identity
return @.p

Dyanamic Sql

Hi I was building a dynamic sql with output please tell me where I am wrong.
Or Tell me right way to do it

My table name is variable (Stock name)
I want close price for given Stock

CREATE PROCEDURE SP_GET_CLOSE
@.TableName as varchar(50),
@.close_price as varchar(50) output

AS
Declare @.SQL NVarChar(1000)
Declare @.high varchar(50)
SET @.SQL = 'Select top 1 @.close_price = [close] from '+ @.TableName+ ' order by trade_date desc'

print @.SQL
Exec sp_executesql @.SQL, N'@.close_price varchar(50) output' , @.close_price
GOWaht abotu this idea?

CREATE PROCEDURE SP_GET_CLOSE
@.TableName as varchar(50),
@.close_price as varchar(50) output

AS
Declare @.SQL NVarChar(1000)
Declare @.high varchar(50)
SET @.SQL = 'Select top 1 [close] from '+ @.TableName+ ' order by trade_date desc'

create table #tmp(ret varchar(450))

insert #tmp
exec(@.SQL)

select @.close_price=cast(ret as money) from #tmp|||Yes That seems a good idea but I have some 200 trading Scripts in month and this would create lot of #tmp tables I thought it should be possible to send dynamic SQl a output variable.
Besides I am having lot of similar quiries in mind for my project

Thanks|||Originally posted by shriya
Yes That seems a good idea but I have some 200 trading Scripts in month and this would create lot of #tmp tables I thought it should be possible to send dynamic SQl a output variable.
Besides I am having lot of similar quiries in mind for my project

Thanks

Temporary table will dropped after procedure execution. BTW, sql server is using temporary objects during any operations (even like select from with order by) so do not worry about this at all.