Hi All,

I want to have a SQL statement which query from a dynamic table inside a
store procedure. However, I have no idea on how to get it done.

For examples:
DECLARE @TABLE AS VARCHAR(10)
SELECT @TABLE = 'MyTable'
SELECT * FROM @TABLE

However, an error message appears "Must declare the variable '@TABLE'" when
I execute the SQL statment. So how can the SQL statement works for selecting
data from a table dynamically? Thanks in advance.

Steven

This shows how:

declare @table as varchar(30);
set @table = 'sys.objects';
exec('select * from '+@table);
go


Ole Kristian

Show quoteHide quote

How can I retrieve and put the result on variable @TABLEID? I wrote the
below sql statement, however, it's not work.

DECLARE @TABLE AS VARCHAR(10)
DECLARE @TABLEID AS INT
SELECT @TABLE = 'MyTable'
SELECT @TABLEID = TABLEID FROM @TABLE



Show quoteHide quote

Steven (a@a.com) writes:
Again, please read http://www.sommarskog.se/dynamic_sql.html and you will
find the answer there. Fumbling around in the dark and inventing your own
syntax is not going to take you very far.

Steven (a@a.com) writes:
There are very good chances that you should not do this at all, or that
if you really need to, that there is a serious flaw in the table design.

I have an article on my web site that explains how you could do it -
and why you probably shouldn't. http://www.sommarskog.se/dynamic_sql.html.

One reason is that you are not properly declaring the table variable, and the second reason is that even if you did, there is no data in the table variable. When you declare a table variable, you must provide it the column definitions.

Here is an example of using a table variable. (The query gathers the Customers that have made Purchases exceeding $100,000.00)

USE Northwind
GO

----

DECLARE @MyTable table
   (    RowId       int     IDENTITY
      , CustomerID  char(5)
      , CompanyName varchar(50)
      , Purchases   decimal
   )

INSERT INTO @MyTable
   SELECT
        c.CustomerID
      , c.CompanyName
      , Purchases = sum( od.UnitPrice * od.Quantity )
   FROM Customers c
      JOIN Orders o
         ON c.CustomerID = o.CustomerID
      JOIN [Order Details] od
         ON o.OrderID = od.OrderID
   GROUP BY
        c.CustomerID
      , c.CompanyName
   HAVING sum( od.UnitPrice * od.Quantity ) > 100000

SELECT
     RowId
   , CustomerID
   , CompanyName
   , Purchases
FROM @MyTable


Show quoteHide quote

Why?  A dynamic table mimics a 1950's scratch tape on a file system
with highly proprietary syntax. It is comfortable for people who have
not learned to think in a declarative language yet and still want to
write code the old way.

You can use derived tables and VIEWs in SQL instead,  Surely you are
not making up new tables on the fly at run time -- that would mean you
have no data model!

Joining 2 select statement with like fields
Views and CTEs performance problem
Adding a space in a field
Can't use Shared Memory protocol to connect
merge two date ranges with overlap from two tables