Need help with Stored Procedure

[sah]

Can someone please help me write a stored procedure? I run a query
(see below) for 50 tables and for every day of the month. I would like
a SP which can provide data with results as follows:

Srv_Name, DB_Name, Table_Name, Date_Of_Records, Total_Records,
Min_SerNumber, Max_SerNumber, Should_Be_Total_Records, Missing_Records
The query I currently run for each table and each single data is
below:

set nocount on
PRINT 'Table: info_table_01
DateTime: BETWEEN 01 sep 2004 00:00 and 01 sep 2004 23:59 '
go
select
count(*) AS 'Total Records' ,
min (SerNumber) AS 'MIN SerNumber'
max (SerNumber) AS 'MAX SerNumber',
max (SerNumber)- min (SerNumber)+1 AS 'Should be total Records',
count(*) - (max (SerNumber)- min (SerNumber)+1) AS 'Missing Records'
from info_table_01
WHERE DateTime BETWEEN '01 sep 2004 00:00' and '01 sep 2004 23:59'
go
RESULTS:

Table: info_table_01
DateTime: BETWEEN 01 sep 2004 00:00 and 01 sep 2004 23:59
TotalRec MinSerNum MMaxSerNum ShouldBeTotalRec MisRec
-------- --------- ---------- ---------------- -------
69023 00054 69090 69027 4
Any help would be very appriciate.

Thanks

[Erland Sommarskog]

sah (do***********@hotmail.com) writes:

Can someone please help me write a stored procedure? I run a query
(see below) for 50 tables and for every day of the month. I would like
a SP which can provide data with results as follows:

Srv_Name, DB_Name, Table_Name, Date_Of_Records, Total_Records,
Min_SerNumber, Max_SerNumber, Should_Be_Total_Records, Missing_Records

Why are there 50 tables to start with? I don't know, but it sounds like a
bad table design to me.

Anyway, there are a couple of options, which one that works the best
depends your evironment. But my primary suggestion would be to create
a inlined table function that spans all these 50 databases. The body of
the function would look like:

select Srv_Name = 'SERVER1', DB_Name = 'db1',
Table_Name = 'info_table1', Date_of_Records = @date
count(*) AS 'Total Records' ,
min (SerNumber) AS 'MIN SerNumber'
max (SerNumber) AS 'MAX SerNumber',
max (SerNumber)- min (SerNumber)+1 AS 'Should be total Records',
count(*) - (max (SerNumber)- min (SerNumber)+1) AS 'Missing
Records'
from SERVER1.db1.dbo.info_table_01
WHERE DateTime >= @date and DateTime < dateadd(DAY, 1, @date)
UNION ALL
select Srv_Name = 'SERVER1', DB_Name = 'db2',
Table_Name = 'info_table2', Date_of_Records = @date
count(*) AS 'Total Records' ,
min (SerNumber) AS 'MIN SerNumber'
max (SerNumber) AS 'MAX SerNumber',
max (SerNumber)- min (SerNumber)+1 AS 'Should be total Records',
count(*) - (max (SerNumber)- min (SerNumber)+1) AS 'Missing
Records'
from SERVER1.db2.dbo.info_table_02
WHERE DateTime >= @date and DateTime < dateadd(DAY, 1, @date)
UNION ALL
...

I made this a table-valued function to accomodate for the date parameter,
but essentially this is the same as a view.

If the set of tables is fairly static, you could develop and maintain
this beast manually. If there are plenty of changes, you may prefer to
write a program that generates the function from an input list. Such
code could be written in T-SQL, but could just as well be written in
C, Visual Basic, Perl or whatever.

Note also, by the way, that I changed the condition for the DateTime
column. I figured that you want to include events from the last minute
of the day as well.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[s sah]

Thanks. I am using only one SQL Server but 50 tables. I need to run the
query every day and for all 50 tables.

Is there a way I can combine the list of all tables from such a query:

SELECT DISTINCT so.name
FROM sysobjects AS so
INNER JOIN syscolumns AS sc
ON so.id=sc.id
WHERE so.name like 't_%' AND sc.name = 'DateTime' AND sc.name
='SerNum'.

Also is there a way to specify how many days I want the records of i.e.
3 days, 7 days or 30 days etc.,

BTW the above query has a little issue too, I have mutiple WHERE filter,
I can not have more then one "AND", currently I am using only one "AND"
and one "OR".

Thanks for your help
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dan Guzman]

You also posted this same question to microsoft.public.sqlserver.server.
Please don't post the same question independently to multiple groups as this
causes duplication of effort.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"s sah" <do***********@hotmail.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

Thanks. I am using only one SQL Server but 50 tables. I need to run the
query every day and for all 50 tables.

Is there a way I can combine the list of all tables from such a query:

SELECT DISTINCT so.name
FROM sysobjects AS so
INNER JOIN syscolumns AS sc
ON so.id=sc.id
WHERE so.name like 't_%' AND sc.name = 'DateTime' AND sc.name
='SerNum'.

Also is there a way to specify how many days I want the records of i.e.
3 days, 7 days or 30 days etc.,

BTW the above query has a little issue too, I have mutiple WHERE filter,
I can not have more then one "AND", currently I am using only one "AND"
and one "OR".

Thanks for your help
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!