Changing a query from Hard Coded to using a table list

like this??  Not quite working...

DECLARE @today datetime
DECLARE @StartDate datetime
DECLARE @EndDate Datetime

DECLARE @SQL varchar (4000)
Declare @tablename varchar (100)

SET @Today = getdate()
SET @Today = CONVERT(VARCHAR(10), @Today-7, 111)
SET @StartDate = DATEADD(DD, 2 - DATEPART(DW, @Today), @Today)
SET @EndDate  = @StartDate + 6

Set @tablename = SELECT CategoryTable from prodSQL1.Prod_Admin.dbo.Categories

set @SQL =
select ' + @TableName + '.id, postdateid, quarkfilename, isrendered, date_posted, category, categoryid,ropadid, convert(varchar(10),postDate,101) as postDate,categoryname, categorytable, newspaperids
from prodSQL1.Prod_Admin.dbo.' + @TableName + '
join prodSQL1.Prod_Admin.dbo.postDates as p on p.id = ' + @TableName + '.postDateID
join prodSQL1.Prod_Admin.dbo.categories as cat on cat.ID = p.CategoryID
where postdate between @StartDate and @EndDate
and IsRendered <> 2'

EXEC (@SQL)

Change:
Set @tablename = SELECT CategoryTable from prodSQL1.Prod_Admin.dbo.Categories
To:
SELECT @tablename = CategoryTable from prodSQL1.Prod_Admin.dbo.Categories

Change:
where postdate between @StartDate and @EndDate
To:
where postdate between '+CAST(@StartDate AS VARCHAR(20))+' and '+CAST(@EndDate AS VARCHAR(20))

ok.  here is my updated code....
DECLARE @today datetime
DECLARE @StartDate datetime
DECLARE @EndDate Datetime

DECLARE @SQL varchar (4000)
Declare @tablename varchar (100)

SET @Today = getdate()
SET @Today = CONVERT(VARCHAR(10), @Today-7, 111)
SET @StartDate = DATEADD(DD, 2 - DATEPART(DW, @Today), @Today)
SET @EndDate  = @StartDate + 6

SELECT @tablename = CategoryTable from prodSQL1.Prod_Admin.dbo.Categories

set @SQL =''
select ' + @TableName + '.id, postdateid, quarkfilename, isrendered, date_posted, category, categoryid,ropadid, convert(varchar(10),postDate,101) as postDate,categoryname, categorytable, newspaperids
from prodSQL1.Prod_Admin.dbo.' + @TableName + '
join prodSQL1.Prod_Admin.dbo.postDates as p on p.id = ' + @TableName + '.postDateID
join prodSQL1.Prod_Admin.dbo.categories as cat on cat.ID = p.CategoryID
where postdate between '+CAST(@StartDate AS VARCHAR(20))+' and '+CAST(@EndDate AS VARCHAR(20))'
and IsRendered <> 2''

EXEC (@SQL)

I get an error: Line 16: Incorrect syntax near '.'.  this line starts: select '+TableName+'id......

updated points...

it would be almost impossible to find this extra or missing . in your code... Just use (execute) your dynamic sql gradually, like exclude all joins and where cases and then add'em one by one until everything works. it is a lot easier for you now to find the syntax error than for us to spot it.

good luck,
yurich

you probably have @TableName as NULL... do this...

instead of EXEC (@SQL).

PRINT @SQL and post the outcome

And when you have found the solution, you may want to question your design, as you have discoverd it is somewhat flawed ...

Sorry, change last line to:
set @SQL = @SQL + ' where postdate between '+CAST(@StartDate AS VARCHAR(20))+' and '+CAST(@EndDate AS VARCHAR(20))+' and IsRendered <> 2'

Man I suck, change last line to:
set @SQL = @SQL + ' where postdate between '''+CAST(@StartDate AS VARCHAR(20))+''' and '''+CAST(@EndDate AS VARCHAR(20))+''' and IsRendered <> 2'

ok.  No errors, but still no data.  

I think it has to do with this row:
SELECT @tablename = CategoryTable from prodSQL1.Prod_Admin.dbo.Categories

The Categories table has multiple rows (CategoryTable).  How does the query know to use these one at a time.  

acperkins, what do you mean that it's flawed?  Do you have recomendations??

You need to add a WHERE clause there like:
SELECT @tablename = CategoryTable from prodSQL1.Prod_Admin.dbo.Categories WHERE CategoryID = 2

ok.  I had a little help from a friend and DirOrbAnt's comment.  Take a look below and comment.

When this runs, it returns data.  I need to know how to insert each returned set into a table.  Where do I add 'Insert into tblXMLDetails' to this code below??


DECLARE @TableName varchar(255)
DECLARE @today datetime
DECLARE @StartDate datetime
DECLARE @EndDate Datetime

DECLARE @SQL varchar (4000)

SET @Today = getdate()
SET @Today = CONVERT(VARCHAR(10), @Today-7, 111)
SET @StartDate = DATEADD(DD, 2 - DATEPART(DW, @Today), @Today)
SET @EndDate  = @StartDate + 6
DECLARE TableCursor CURSOR FOR

SELECT CategoryTable as TableName FROM categories

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

print @TableName + ' - Table to Query'

set @SQL = 'select TheTable.id, postdateid, quarkfilename, isrendered, date_posted, category, categoryid,ropadid, convert(varchar(10),postDate,101) as postDate,categoryname, categorytable, newspaperids'
set @SQL = @SQL + ' from prodSQL1.Prod_Admin.dbo.' + @TableName + ' TheTable'
set @SQL = @SQL + ' join prodSQL1.Prod_Admin.dbo.postDates as p on p.id = TheTable.postDateID'
set @SQL = @SQL + ' join prodSQL1.Prod_Admin.dbo.categories as cat on cat.ID = p.CategoryID'
set @SQL = @SQL + ' where postdate between '''+CAST(@StartDate AS VARCHAR(20))+''' and '''+CAST(@EndDate AS VARCHAR(20))+''' and IsRendered <> 2'

print @SQL
EXEC (@SQL)
FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

Replace:
set @SQL = 'select TheTable.id, postdateid, quarkfilename, isrendered, date_posted, category, categoryid,ropadid, convert(varchar(10),postDate,101) as postDate,categoryname, categorytable, newspaperids'
With:
set @SQL = 'Insert into tblXMLDetails ([id], postdateid, quarkfilename, isrendered, date_posted, category, categoryid, ropadid, postDate, categoryname, categorytable, newspaperids)'
set @SQL = @SQL + ' select TheTable.id, postdateid, quarkfilename, isrendered, date_posted, category, categoryid,ropadid, convert(varchar(10),postDate,101) as postDate,categoryname, categorytable, newspaperids'

Sweet!  Dire, thanks a lot for your help.  It's been a great learning experience.  

:)