Dynamic v/s Static - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic v/s Static

Author

Topic

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-02-23 : 06:45:54

can anybody tell me, what are the Dynamic and Static SQL statements are?, with example or any useful link?

does T-SQL means Dynamic SQL and query without parameters means Static SQL?

thanks in advance,

Mahesh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-23 : 07:05:11

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

Dynamic SQL just means the query is form dynamically during run time.

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-02-23 : 09:06:23

so does T-SQL means Dynamic SQL and query without parameters means Static SQL?

Mahesh

cas_o
Posting Yak Master

154 Posts

Posted - 2007-02-23 : 09:29:11

No T-SQL is Microsofts proprietry implementation of the SQL database language.

Static sequal includes statements with parameters eg:

declare @id int
set @id = 1
select * from authors where au_id = @id

A DYNAMIC statement is created (by concatenating a SQL statement string) at run time and then executed eg:
declare @sql nvarchar(4000)
declare @id char(1)
set @id = '1'
set @sql = 'select * from authors where au_id = ' + @id

exec sp_ExecuteSql @sql

The above dynamic example is somewhat contrived and you wouldn't use it in this particular way. More likely it's where you need to make things like the table name in the statement dynamic because in the traditional static way you can't pass table names as parameters eg:

declare @sql nvarchar(4000)
declare @tbName nvarchar(32)
declare @id char(1)

set @tbName = 'tbMyTable'
set @id = '1'
set @sql = 'select * from ' + @tbName + ' where au_id = ' + @id

exec sp_ExecuteSql @sql

;-]... Quack Waddle

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-23 : 09:40:26

When's the interview/test?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-02-23 : 10:09:12

quote:
Originally posted by X002548

When's the interview/test?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

hi, X002548. its not test. i came accross the dynamic & static query type, n as eagerness, i wanted to know. do u have any information regarding to STATIC QUERIES? let me know.

thanks in advance

Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-23 : 10:38:38

Static Query: Any query which is not dynamic is static query
Dynamic Query: Already defined by khtan.

Hope this clears your doubt!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-23 : 11:48:53

[code]
CREATE PROC mySproc99
as

DECLARE @dynamicSQL varchar(8000)

-- Dynamic SQL
SET @dynamicSQL = 'SELECT * FROM Orders'
EXEC(@dynamicSQL)

--Static SQL

SELECT * FROM Orders
GO

EXEC mySproc99
GO

DROP PROC mySproc99
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-02-26 : 01:28:11

quote:
Originally posted by harsh_athalye

Static Query: Any query which is not dynamic is static query
Dynamic Query: Already defined by khtan.

Hope this clears your doubt!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Yes,

Mahesh

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-02-26 : 02:07:08

Well, I beliave its all lies in the defination of Static and Dynamic word itself.. As i belive most of the programming terms is always linked with the real world terms

Static means, its not going to change, which is stationary.. so if you are linking to the SQL SERVER Queries, so you query wont change at run time. It will remain stagnent.

Dynamic Means somthing which is moving, which is not stagnent.. so the Dynamic Queries are those who are changed at the run time..

The Example which Brett stated is the perfect example of it..

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-26 : 02:37:30

"The Example which Brett stated is the perfect example of it.."

Except that it is less than ideal example of dynamic query because nothing is changing at the runtime. I think a better example can be:

CREATE PROC mySproc99
( @TblName Varchar(255))
as

DECLARE @dynamicSQL varchar(8000)

-- Dynamic SQL (TableName is decided at runtime)
SET @dynamicSQL = 'SELECT * FROM ' + @TblName
EXEC(@dynamicSQL)

--Static SQL (TableName is fixed here)
SELECT * FROM Orders
GO

EXEC mySproc99 'Orders'
GO

DROP PROC mySproc99
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-02-26 : 03:20:14

oks.. Agreed thatz the perfect Example..

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources