• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 972
  • Last Modified:

Using variable for table name

hello experts,

how can i use a variable for the table name?

declare @table_name varchar(2000)

set @table_name = 'mytable'

select *
  from @table_name

I would have thought it was that easy, but it wants me to declare the table variable.  i read up on table variables, and that talks about storing values...not wanting to do that.

thanks
0
thewayne73
Asked:
thewayne73
  • 2
1 Solution
 
TimCotteeHead of Software ServicesCommented:
Hello thewayne73,

declare @table_name varchar(2000)

set @table_name = 'mytable'

Exec ('select *
  from ' + @table_name)

Regards,

TimCottee
0
 
TimCotteeHead of Software ServicesCommented:
thewayne73,

You have to use Dynamic sql, in otherwords build your required statement as a string and then use exec() to actually execute the statement. Sql Server does not do direct macro replacement in the way you were thinking.

TimCottee
0
 
indianguru2Commented:

DECLARE @Query NVARCHAR(2000)
DECLARE @table_name VARCHAR(50)
SET @table_name = 'mytable'
 
SET @Query = 'SELECT  * FROM ' + @table_name
 
EXECUTE sp_executesql @Query

Open in new window

0
 
thewayne73Author Commented:
Thanks...i was creating dynamic sql..but that can end up looking nasty.  using this solution will work for most of what i am dong.

wayne
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now