Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Denver, Colorado
    Posts
    2

    Unanswered: 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.com

  2. #2
    Join Date
    Jan 2003
    Location
    Surrey, UK
    Posts
    23
    My 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


  3. #3
    Join Date
    Jan 2003
    Location
    Denver, Colorado
    Posts
    2
    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 :-)

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    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

  5. #5
    Join Date
    Jan 2003
    Location
    Surrey, UK
    Posts
    23
    No worries!
    Andy


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •