INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

Executing Dynamically Created T-SQL Strings by sunila7
Posted: 6 Feb 03

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.

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close