Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    73

    Unanswered: Counting Records in a Stored Procedure

    I am trying to count records in my stored procedure. Can someone please help me.

    these are the two procedures I am using
    Code:
    Alter Procedure usp_rptQualityReport As
    
    SELECT 
    	tblRMAData.RMANumber,
    	tblRMAData.JobName, 
    	tblRMAData.Date, 
    	tblFailureReasons.LintItemID, 
    	tblLineItems.Qty, 
    	tblLineItems.Model, 
    	tblLineItems.ReportDate, 
    	tblFailureReasons.FailureReason, 
    	tblTestComponentFailures.ComponentID, 
    	tblTestComponentFailures.FailureCause
    FROM 
    	tblRMAData INNER JOIN ((tblLineItems INNER JOIN tblTestComponentFailures ON tblLineItems.ID = tblTestComponentFailures.LineItemID) INNER JOIN tblFailureReasons ON tblLineItems.ID = tblFailureReasons.LintItemID) ON tblRMAData.RMANumber = tblLineItems.RMANumber
    
    WHERE 
    	(((tblFailureReasons.FailureReason) <> N'NONE'))
    
    ORDER BY
    	tblFailureReasons.FailureReason
    Code:
    Alter Procedure usp_rptQualityReport2 As
    
    exec usp_rtpQualityReport
    
    SELECT
    	usp_rptQualityReport.RMANumber, 
    	usp_rptQualityReport.JobName, 
    	usp_rptQualityReport.Date, 
    	usp_rptQualityReport.LintItemID, 
    	usp_rptQualityReport.Qty, 
    	usp_rptQualityReport.Model, 
    	usp_rptQualityReport.ReportDate, 
    	usp_rptQualityReport.FailureReason, 
    	usp_rptQualityReport.ComponentID, 
    	usp_rptQualityReport.FailureCause, 
    
    (SELECT COUNT(FailureReason) FROM usp_rptQualityReport a WHERE a.FailureReason=usp_rtpQualityReport.FailureReason) AS groupingLevel
    	
    
    FROM usp_rptQualityReport;

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What abotu this idea?

    create procedure test
    as
    select * from sysobjects
    go
    create procedure test2
    as
    exec test
    select @@rowcount
    go
    test2

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Are you counting number of lines in your stored procedure or number of records your stored procedure returns?


    For number of lines check syscomments, for number of rows either use Profiler of alter your procedure, pass an OUTPUT parameter, and store the value of @@ROWCOUNT variable into it immediately after your query and before RETURN (yes, put a RETURN (0) statement at the end of your procedure)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Apr 2004
    Posts
    73
    I am still trying to make this work and made some changes and created a view in SQL. But I still can't get them to count and then rank the records.

    I am still having a problem counting the number of each type of Failure Reason.

    Here is the Stored Procedure I am using:
    Code:
    Alter Procedure usp_rptQualityReport3 As
    
    SELECT * FROM viewQualityReport
    
    (SELECT COUNT(FailureReason)) AS groupingLevel
    
    WHERE 
    	(((viewQualityReport.FailureReason) <> N'NONE'))
    
    ORDER BY
    	groupinglevel desc

Posting Permissions

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