[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using variable for table name

Posted on 2008-11-19
4
Medium Priority
?
969 Views
Last Modified: 2012-05-05
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
Comment
Question by:thewayne73
  • 2
4 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 2000 total points
ID: 22994708
Hello thewayne73,

declare @table_name varchar(2000)

set @table_name = 'mytable'

Exec ('select *
  from ' + @table_name)

Regards,

TimCottee
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 22994716
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
 
LVL 11

Expert Comment

by:indianguru2
ID: 22994793

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
 

Author Closing Comment

by:thewayne73
ID: 31518259
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question