Executing Dynamically Created T-SQL Strings

Very frequently asked questions in any SQL Server forum are,
how to pass in a table name to a select statement and use that to get the result set or
Is it possible to declare a dynamic cursor within a sp where the table name is passed as a parameter at runtime or
pass in a list of values to a Stored Procedure and get the result from a select statement using an IN operator.

Now, All this question have the same answer, you can create dynamically created T-SQL string and it can be executed using the EXECUTE command

E.g. 1: Example with a List of values to be used in an IN List of a Select Statement

Create table #TempTable(EmpId int, EmpName Varchar(40))

Insert Into #Temptable Values (1, 'Sunil')
Insert Into #Temptable Values (2, 'John')
Insert Into #Temptable Values (3, 'Mark')
Insert Into #Temptable Values (4, 'Davies')
Insert Into #Temptable Values (5, 'Kevin')
Insert Into #Temptable Values (6, 'Gary')
Insert Into #Temptable Values (7, 'Bob')
Insert Into #Temptable Values (8, 'Charlie')

Declare @INList Varchar(1000)

Declare @SQL Varchar(1000)

Set @INList = '1,2,3,4,5'

Set @SQL = 'SELECT * From #TempTable Where EmpID in (' + @INList +')'
Exec(@SQL)

E.g. 2: How to Pass a table name to select statement

Use Pubs
Declare @tblName Varchar(40)
Declare @SQL Varchar(500)

Set @tblName = 'Authors'
SET @SQL = 'Select * from ' + @Tblname
Exec(@SQL)

E.g. 2: How to open a Cursor Dynamically using Exec Statement

Use Pubs
Declare @tblName Varchar(40)
Declare @SQL varchar(2000)
Set @tblName = 'Authors'

SET @SQL = 'DECLARE Dyn_cursor CURSOR
FOR SELECT * FROM ' + @tblName

Exec(@SQL)

Open Dyn_Cursor

<YOUR REST OF CODE here as usual>

Close Dyn_cursor
Deallocate Dyn_cursor


Statements executed are not compiled until the EXECUTE statement is executed. So one should be careful while using this, though with proper indexes and query tuning this can be minimized.

If the Execute Statement is used to change the database, it works only until the end of the Execute Statement. So, If an execute statement Like Execute 'Use Master' is executed the database context reverts back to which ever database was selected before the execute statement was executed

Hope this is helpful and any comments,suggestions, additions
are always welcome.