Dynamic Query in Sql Server

It is used commonly when you are Selecting the Result set on the base of some parameter or condition ,  or to optimize the Sql to generate the accurate query at Runtime according to the Requirements. .

suppose we have a Stored Procedure which takes a single Parameters , which is a table name and generates the query according to it.

Here is a simple Example below:

Create procedure DynamicQuery

(

@tableName varchar(33)

)

as

BEGIN

Declare @SelectQuery Varchar(25)

If (@tableName = 'Teacher')

set @SelectQuery = 'select * from Teacher'

exec (@SelectQuery)

If (@tableName = 'Student')

set @SelectQuery = 'select * from Student'

exec (@SelectQuery)

END