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
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
<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.