Avatar of Scotto13
Scotto13
 asked on

Changing a query from Hard Coded to using a table list

My company has a SQL database that contains many tables - a different table for each client.  There is one table that is used to identify the tables.  

I wrote this query and one for each client and now want to change it to adapt to the tables listed.  

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

The table - VancouverIsland is identified in a table called Categories in a field called 'CategoryTable'.  

So, do I something like this?

Declare @tablename string
Set @table name = dbo.Categories.CategoryTable
select @tablename.id, postdateid...

Please help.
Scotto13
Microsoft SQL Server

Avatar of undefined
Last Comment
DireOrbAnt

8/22/2022 - Mon
SOLUTION
Einstine98

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scotto13

ASKER
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)
DireOrbAnt

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))
Scotto13

ASKER
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......
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Scotto13

ASKER
updated points...
Yurich

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
Einstine98

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

instead of EXEC (@SQL).

PRINT @SQL and post the outcome
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anthony Perkins

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
DireOrbAnt

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'
DireOrbAnt

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'
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scotto13

ASKER
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??
DireOrbAnt

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

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DireOrbAnt

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'
Scotto13

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

:)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23