This Storeprocedure is Correct way ?..

  • I am doing a MIS Form.I select multiple table to get a data as single row.this Store procedure is correct

    USE [smvmch]

    GO

    /****** Object: StoredProcedure [dbo].[spMISStudentFees] Script Date: 02/14/2012 17:55:49 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author: Tamil Selvan Selvaraj

    -- Create date: 12-02-2012

    -- Description: MIS Detail For Student Fees Detail

    -- =============================================

    CREATE PROCEDURE [dbo].[spMISStudentFees]

    @FromDateDATETIME,

    @ToDateDATETIME

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    DECLARE @FeesCollection INT,@FeesOutStanding INT,@Cancel INT,@Refund INT

    DECLARE @ExcessFees INT,@NoOfStudent INT

    SELECT @FeesCollection = SUM(SBD.Amount) FROM SStudentBilling SB

    INNER JOIN SStudentBillingDetail SBD ON SB.BillNo = SBD.BillNo

    WHERE BillDate BETWEEN CONVERT(Date,@FromDate) AND CONVERT(Date,@ToDate)

    SELECT

    @Cancel = SUM(Cancel)

    ,@Refund = SUM(Refund)

    FROM

    (SELECT CASE WHEN CR.BillType = 1 THEN SUM(CRD.Amount) ELSE 0 END AS Cancel,

    CASE WHEN CR.BillType = 2 THEN SUM(CRD.Amount) ELSE 0 END AS Refund

    FROM StudentCancelRefund CR

    INNER JOIN StudentCancelRefundDetail CRD ON CR.BillCancelRefundNo = CRD.BillCancelRefundNo

    WHERE CR.CancelRefundDate BETWEEN CONVERT(Date,@FromDate) AND CONVERT(Date,@ToDate)

    GROUP BY CR.BillType) AS Can

    SELECT @FeesOutStanding = SUM(Balance) FROM SStudentFees SF

    INNER JOIN SStudent S ON SF.StudentID = S.StudentID

    WHERE ParticularID <> 7 AND S.IsActive = 1 AND Balance>0

    SELECT @ExcessFees = SUM(Balance) FROM SStudentFees SF

    INNER JOIN SStudent S ON SF.StudentID = S.StudentID

    WHERE ParticularID = 7 AND S.IsActive = 1

    SELECT @NoOfStudent = COUNT(StudentID) FROM StudentFeesModification WHERE ReferenceDate BETWEEN CONVERT(Date,@FromDate) AND CONVERT(Date,@ToDate)

    SELECT ISNULL(@FeesCollection,0) AS FeesCollection

    ,ISNULL(@FeesOutStanding,0) AS FeesOutStanding

    ,ISNULL(@Cancel,0) AS Cancel

    ,ISNULL(@Refund,0) AS Refund

    ,ISNULL(@ExcessFees,0) AS Excess

    ,ISNULL(@NoOfStudent,0) AS NoOfStudent

    END

  • I can't tell whether it's correct without knowing what it's supposed to do. Does it run? Does it return the results you want? If yes to both, it's probably correct.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply