forum.venkateswarlu.net
      Register      Login
Dynamic table name in sql query

1 replies to this topic

Gaurav Dutt #1
Member
62 Points
 Posted on 04 Apr 2012 06:10 PM IST Hi,

I want to provide dynamic table name in my sql query
Create procedure usp_orders
(
@order_date datetime,
@product varchar (250),
@product_id varchar (100)
)
as
declare @temptable varchar(100)
set @temptable = 'orders'
begin
select * from @temptable
end
Go
-------------

it gives me error:
Must declare the table variable "@temptable".

how can i provide dynamic table name in select or insert query in a sp.



Thanks 
SQL Server     654 views     Reply to this topic
Jahnavi N #2
Member
164 Points
 Replied on 18 Jun 2012 05:29 PM IST Hi Gaurav Dutt,

Here i am modifying the procedure. I tested it, and working fine.

Create procedure usp_orders
(
@order_date datetime,
@product varchar (250),
@product_id varchar (100)
)
as
BEGIN

declare @temptable varchar(100)
set @temptable = 'orders'

DECLARE @SQL_QUERY VARCHAR(MAX)
SET @SQL_QUERY = 'select * from ' + @temptable

EXEC( @SQL_QUERY )

END

EXEC command will executes the SQL Query which is placed in parenthesis ().

Thanks
Jahnavi 
Reply to this topic