Sep 9, 2010

How to specify table name dynamically in a SQL statement + SQL SERVER 2008

Dynamic SQL :
Using a dynamic sql is quite comman in sql statement. I had a situation where
i have to pass the table file as a argument to a Stored Procudure and depending 
upon the argument i have to execute the Select statement.

Say for eg.
argument @Table = dbo.Customers
But this doesnt work.
So when you are in a need to specify any of the objects like table or column etc
we need to use dynamic SQL using exec method. Below is an example

DECLARE @TableName varchar(50)
SET @TableName ='dbo.Customers'
DECLARE @SQL varchar(max)
SET @SQL = 'SELECT * FROM ' +@TableName // dynamic table name

No comments: