Static Vs Dynamic Queries

Static
By static SQL we mean SQL code written once in the development phase when database and query structures are known. Static SQL is usually targeted at a specific database and in many cases gets stored in stored procedures. Many applications (especially Enterprise Applications) reach a stage where some dynamic data manipulation is required and static SQL techniques no longer suffice. Common examples are custom reports and filters designed by an application user. In order to support such functionality, an application must exhibit UI to define the report or filter, UI to display the results and an engine which is able to render SQL SELECT statement from the report definition. Here Sql.Net steps in to automate the process of SQL generation in a convenient, time saving, database independent way.

Dynamic
Dynamic SQL generation is also needed in applications where the databases structure itself is dynamic. Many Enterprise Applications allow users to customize the way data is stored and displayed. This can involve columns being added or deleted or whole database tables dropped and created. Static SQL can not be written to work with dynamic structure because identifier names can not be substituted with variables. Therefore, SQL statements which manipulate or fetch data from non static storages must be created and rendered programmatically in runtime. Using Sql.Net for that purpose relieves your organization of the necessity to hand-code SQL renderers.


Some More Definitions

Ist
Static query will create an execution plan during compilation.
Dynamic query will create execution plan at run time.
It’s very slow in execution since building the plan at run time makes it too slow.

2nd
When your query pass with value (static)
and when your query pass with parameter like (@user_name)
is dynamic query.

Static query
select * from TblEmpDetail where user_name='Kapil'

Dynamic query
select * from TblEmpDetail Where user_name=@user_name
where @user_name=sqlparameter