Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Question Unanswered: Stored Procedure help

    Hello. I have a stored procedure that gets various stats for our handheld applications. The code is below:


    CREATE PROCEDURE GetSyncDBStatistics
    AS
    BEGIN
    SELECT TOP 100 M.MasterNameFirstName + ' ' + M.MasterNameLastName 'Person',
    CASE
    WHEN (S.hotsyncend IS NULL) AND (DATEDIFF(second,S.hotsyncstart,GETDATE()) < 900) THEN 'UPLOADING'
    WHEN (S.hotsyncend IS NULL) AND (DATEDIFF(second,S.hotsyncstart,GETDATE()) >= 900) THEN 'FAILED'
    WHEN (S.hotsyncend IS NOT NULL) THEN 'COMPLETE'
    END 'Status',
    CONVERT(varchar(50),CONVERT(datetime,S.hotsyncstar t),100) 'Start Time',
    CONVERT(varchar(50),CONVERT(datetime,S.hotsyncend) ,100) 'End Time',
    CONVERT(varchar(50),DATEDIFF(second,S.hotsyncstart ,S.hotsyncend)) 'Seconds'
    FROM SyncDBPalm S
    INNER JOIN History H ON H.HistoryID = S.PMCSessionID
    INNER JOIN LoginUser L ON L.LoginUserID = H.LoginUserID
    INNER JOIN MasterName M ON M.MasterNameID = L.MasterNameID
    --LEFT OUTER JOIN PalmSync P ON P.PMCSessionID = S.PMCSessionID
    WHERE M.MasterNameLastName != 'dbo'
    ORDER BY convert(datetime,S.hotsyncstart) DESC

    END

    SET NOCOUNT OFF

    END

    My question is, I have another stored procedure that I want to use with this one. I want this second one to take the username(M.MasterNameFirstName), starttime (S.hotsyncstart), and endtime(S.hotsyncend) from this procedure and is it as it's parameters. Here's what I have written for the second store procedure so far:

    CREATE PROCEDURE GetCount (@User varchar(10), @starttime varchar(50), @endtime varchar(50))

    AS
    BEGIN

    SET NOCOUNT ON

    SELECT
    count(*) 'SurveyCount'
    FROM
    Audit a
    WHERE
    modifyuserid = @user
    AND
    a.auditstart < @endtime
    AND
    a.auditstart > @starttime

    SET NOCOUNT OFF

    END
    GO

    So, is there any easy way to accomplish this? I want them all to appear in the same table. I'm new to stored procedures and feel a bit overwhelmed. Any advice will be greatly appreciated.

  2. #2
    Join Date
    Mar 2004
    Posts
    8
    It may be easier for you to just work that one extra field into your original query, as a sub-query in the SELECT, such as:

    SELECT TOP 100 M.MasterNameFirstName + ' ' + M.MasterNameLastName 'Person',
    CASE
    WHEN (S.hotsyncend IS NULL) AND (DATEDIFF(second,S.hotsyncstart,GETDATE()) < 900) THEN 'UPLOADING'
    WHEN (S.hotsyncend IS NULL) AND (DATEDIFF(second,S.hotsyncstart,GETDATE()) >= 900) THEN 'FAILED'
    WHEN (S.hotsyncend IS NOT NULL) THEN 'COMPLETE'
    END 'Status',
    CONVERT(varchar(50),CONVERT(datetime,S.hotsyncstar t),100) 'Start Time',
    CONVERT(varchar(50),CONVERT(datetime,S.hotsyncend) ,100) 'End Time',
    CONVERT(varchar(50),DATEDIFF(second,S.hotsyncstart ,S.hotsyncend)) 'Seconds',
    (SELECT count(*) FROM Audit a
    WHERE a.modifyuserid = M.MasterNameFirstName
    AND a.auditstart < S.hotsyncend
    AND a.auditstart > S.hotsyncstart) AS 'SurveyCount'
    FROM SyncDBPalm S
    INNER JOIN History H ON H.HistoryID = S.PMCSessionID
    INNER JOIN LoginUser L ON L.LoginUserID = H.LoginUserID
    INNER JOIN MasterName M ON M.MasterNameID = L.MasterNameID
    --LEFT OUTER JOIN PalmSync P ON P.PMCSessionID = S.PMCSessionID
    WHERE M.MasterNameLastName != 'dbo'
    ORDER BY convert(datetime,S.hotsyncstart) DESC

    Or is there a particular reason that you really wanted to use a second stored procedure?

  3. #3
    Join Date
    Nov 2003
    Posts
    6
    Thank you so much! That is exactly what I needed. Thank you, thank you, thank you!

Posting Permissions

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