Call a stored procedure from another SP?

Hello,

I'm trying to get a dynamic Web log report out of SQL Server 2000. I am sending all of my IIS activity to a SQL Server table called inetlog. I need to get a summary of downloads for particular files, and within a certain time period. So, I start by getting unique file names from the table by building a view:

CREATE VIEW UniqueFileNames
AS
SELECT TOP 100 PERCENT COUNT (t.target) AS NumDownloads,
t.target

FROM inetlog t

GROUP BY t.target
ORDER BY t.NumDownloads DESC

GO

Now that I've got this I can run my stored procedure which passes a number representing the days within which the downloads occurred:

CREATE PROCEDURE sp_DownloadsWithinNumDays

@.NumDays INT

AS

SELECT u.NumDownloads / 3 AS Downloads,
LEFT(u.Target,(LEN(u.Target)-4)) AS DocName,
UPPER(RIGHT((RTRIM(u.Target)),3)) AS DocType,
CONVERT(CHAR(8), MAX(i.logtime), 1) AS LogDate

FROM UniqueFileNames u

INNER JOIN
inetlog i
ON u.target = i.target

WHERE DATEDIFF(DAY, i.logtime, GETDATE()) <= @.NumDays AND
(u.Target LIKE '%.pdf%' OR
u.Target LIKE '%.doc%' OR
u.Target LIKE '%.zip%')

GROUP BY
u.target,
u.NumDownloads

ORDER BY Downloads DESC

GO

This works just fine - I pipe my results to a Web page and I get a list of *.doc, *.pdf, and *.zip files downloaded within the past x days, including a count. The problem is that the count never changes. If I put 10 days in the SP, I get a list of files downloaded within the last 10 days, but the count shows ALL downloads of that file. If I put 5 days, I get the file downloads from the past 5 days, with the same counts (i.e., all downloads).

I know why this is happening: it's because the view that I join to is the count of all downloads, but what I can't figure out is how to dynamically generate this count so that the input parameter for the SP (num days) is used.

Sorry for the long post, but I'm getting myself confused, so I thought I'd be very explicit. Thanks for any help you can offer.

Dylan
dylan_thomas@.esri.comMy understanding:

You want the count of downloads in the last X days for each file downloaded in the last X days...

Forget the view and try this proc.... unless I missed something...

CREATE PROCEDURE sp_DownloadsWithinNumDays

@.NumDays INT

AS

DECLARE
@.var_FromDate as datetime

SET NOCOUNT ON

SET @.var_FromDate = DATEADD ( d , 0 - @.NumDays, GETDATE() )

SELECT COUNT(*) AS Downloads,
LEFT(i.Target,(LEN(i.Target)-4)) AS DocName,
UPPER(RIGHT((RTRIM(i.Target)),3)) AS DocType,
CONVERT(CHAR(8), MAX(i.logtime), 1) AS LogDate

FROM inetlog i

WHERE i.logtime > @.var_FromDate AND
(i.Target LIKE '%.pdf%' OR
i.Target LIKE '%.doc%' OR
i.Target LIKE '%.zip%')

GROUP BY
i.target

ORDER BY 1 DESC

GO|||Andy - Fantastic! Runs faster, and gives me the result that I need. I'm very new to SQL Server (if you couldn't tell!), so it's good to see how you've used a variable in this procedure. Opens up a whole new world of possibilities for a SQL-based doc management system that I'm working on.

Thanks :-)|||CREATE PROCEDURE sp_DownloadsWithinNumDays
@.NumDays INT
AS
SELECT
u.NumDownloads / 3 AS Downloads
,LEFT(u.Target,(LEN(u.Target)-4)) AS DocName
,UPPER(RIGHT((RTRIM(u.Target)),3)) AS DocType
,CONVERT(CHAR(8), MAX(i.logtime), 1) AS LogDate
FROM
(
SELECT Target,COUNT(Target) AS NumDownloads
FROM inetlog
WHERE DATEDIFF(DAY, logtime, GETDATE()) <= @.NumDays AND
(
Target LIKE '%.pdf%' OR
Target LIKE '%.doc%' OR
Target LIKE '%.zip%'
)
GROUP BY Target
) u
INNER JOIN inetlog i ON u.target = i.target
WHERE DATEDIFF(DAY, i.logtime, GETDATE()) <= @.NumDays AND
(
i.Target LIKE '%.pdf%' OR
i.Target LIKE '%.doc%' OR
i.Target LIKE '%.zip%'
)
ORDER BY Downloads DESC, Target ASC|||No worries!