Looping through tables
-
I have a set of select statements that work well when ran by itself referencing a single table for a report. I would like to be able to place a variable of sorts so that this report can be ran on multiple tables in a single exec. Here is the 'static' set of selects:
select count(distinct docid) as "Total Non-Indexed Orphans for " from dbo.ae_dl11
where docid not in
(select docid from dbo.ae_dt11)
select count(*) as "Total Indexed Documents" from dbo.ae_dt11
select sum(numobjects) as "Total Indexed Pages" from dbo.ae_dt11
<end scripts>
The ae_dtnn and ae_dlnn table numbers can be retrieved with this select statement:
select appid from dbo.ae_apps
So I would like to be able to run the above set of selects for each appid found in the ae_apps table. I tried declaring something like this:
declare
@appid nvarchar(4),
@dl nvarchar(11),
@dt nvarchar(11)
set @dl = 'sysop.ae_dl'
set @dt = 'sysop.ae_dt'
set @appid = 'select appid from sysop.ae_apps'
and then attempting to concatenate two variables like select * from @dt + @appid but obviously this bombs. Could someone be so kind to point me in the right direction to build this correctly so that I don't have to keeping hitting CTRL-H and manually replacing the appid for the ae_dt and ae_dl tables.
Much thanks!!!
-Ron
-
I don not know what exactly you want to do. do you want to run all of these queries or always only on of it.
This is a solution when you like to run only on query.
depending on the value you will set it queries the different statement.
declare @nStmtCall int
select @nStmtCall = 1
if @nStmtCall = 1
begin
select count(distinct docid) as "Total Non-Indexed Orphans for "
from dbo.ae_dl11
where docid not in (select docid from dbo.ae_dt11)
end
if @nStmtCall = 2
begin
select count(*) as "Total Indexed Documents" from dbo.ae_dt11
end
if @nStmtCall = 3
begin
select sum(numobjects) as "Total Indexed Pages" from dbo.ae_dt11
end
-
Michael thank you for the reply.
I thought i was being clear in what it was I was trying to do, however it is obvious that I was not. Let me try again..
I have a set of tables that are prefixed with ae_dtnn and ae_dlnn. the ae_dt tables contain index information as well as the docid. The ae_dl tables reference those docid's physical path of storage. So ae_dt01 is the table that holds ''Accountings' documents and ae_dt02 if for 'Payroll' and on and on. The table ae_apps is the reference to each of these values that link ae_dt01 to 'Accounting'.
The three select statements ideally would be shot off for each instance of a ae_dtnn so to give me some stats as Joe has surmised. The results will look like the following for a particular instance;
Total Non-Indexed Orphans
--------------------------
15
Total Indexed Documents
-----------------------
8278
Total Indexed Pages
-------------------
30072
Being that there are a couple hundred ae_dt/ae_dl tables I was attempting to figure out the method to not have to manually replace the nn for each of the three selects but do it with variables based on the select appid from ae_apps statement to run the three selects for each appid found in the ae_apps table.
Is that any clearer? I appreciate the the help very much as again, the 'clever' Joe has once again surmised that my level of knowledge could be enhanced so that perhaps, one day there will not be a need for forums such as these that are meant for people asking questions and we all can exist in a state of perfection where all of our questions are already answered before they come up. Then perhaps those that have already achieved this level of perfection might not feel compelled to come to such forums and thrash those that have not yet attained this level...
-Ron
-
Ronald:
To answer your specific question (if I understand it correctly), this will do what you ask (I've just typed this out without testing so it may need a tweak):
DECLARE @appid nvarchar(4)
DECLARE @sql nvarchar(250)
DECLARE appid_cursor CURSOR FOR
SELECT appid FROM ae_apps
OPEN appid_cursor
FETCH NEXT FROM appid_cursor INTO @appid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'SELECT COUNT(DISTINCT docid) AS [Total Non-Indexed Orphans] FROM dbo.ae_dl' + @appid + '
where docid not in
(select docid from dbo.ae_dt' + @appid + ')'
EXEC(@sql)
SELECT @sql = 'select count(*) as [Total Indexed Documents] from dbo.ae_dt' + @appid
EXEC(@sql)
SELECT @ sql = 'select sum(numobjects) as [Total Indexed Pages] from dbo.ae_dt' + @appid
EXEC(@sql)
FETCH NEXT FROM appid_cursor INTO @appid
END
CLOSE appid_cursor
DEALLOCATE appid_cursor
However, I would have to say that I would never use this particular solution (as Joe put it I seem to be the one who has chosen to "sell you poison") and agree entirely with Joe's point regarding database design.
I don't know the complete details of your design, but it seems almost certainly to be the case that the "appid" you have appended onto multiple table names could be handled by one table, ae_dt, with appid as part of its primary key. This is a simple solution and yours may be more complex, but there always is one.
As well as making more sense, the change of design would mean you would no longer require a cursor
, and could avoid any number of other issues in the future.Re-hashing a flawed database design after you've implemented it can be a pain in the backside (not as much as re-hashing someone else's though
) but is worth it in the long run.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply