What is the diff between Static Queries and Dynamic queries
give me some examples
Answers were Sorted based on User's Feedback
Answer / praveen
Static queries are the queries we normally use like SELECT *
FROM EMPLOYEE. where as dynamic queries can be built and
executed dynamically. sp_executesql system stored procedure
is used to execute dynamic sql statements.
Eg: sp_executesql N'select * from employee'
dynamic sql queries will take more time to execute when
compared to static queries
Is This Answer Correct ? | 18 Yes | 5 No |
Static Queries are permanent and cannot be changed during run-time, like: "SELECT * FROM Employees"
Dynamic Queries can be changed during run-time as they are created by using variables and these variables contain parts of SQL Query, like:
DECLARE @SQL VARCHAR(MAX)
DECLARE @WHENSQL VARCHAR(2000)
DECLARE @SEARCHSQL VARCHAR(500)
SELECT @WHENSQL = 'EmployeeID'
SELECT @SEARCHSQL = '100'
SELECT @SQL = 'SELECT * FROM Employees WHERE ' + @WHENSQL + ' = ' + @SEARCHSQL
EXEC (@SQL)
-- or
EXEC sp_executesql @SQL
For more interview Questions on SQL Server: http://sqlwithmanoj.wordpress.com/interview-questions/
Is This Answer Correct ? | 1 Yes | 0 No |
How to verify a user name with sqlcmd tool?
What is co-related sub query?
what are the advanced features in sql 2008?
What is log shipping? Can we do logshipping with SQL Server 7.0 ?
Which language is supported by sql server?
How to select nth record from a table?
13 Answers DELL, Microsoft, Ramco,
logshipping is Any difference 2000 and 2005?
What is use of except clause? How does it differ from not in clause?
How to provide default values to stored procedure parameters?
What is @@rowcount in sql?
What is raiseerror? What is raiseerror?
Can you explain different types of locks in sql server?