我的存储过程无法在应用程序中显示任何记录 [英] My stored procedure cannot show any record in application
问题描述
USE [EMO]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_RptDailySummaryPTCLBillsCollection_Result]
-- Add the parameters for the stored procedure here
@DateFrom Date=Null,
@DatTo Date=Null,
@SubOfficeID varchar(200),
@GroupID int,
@ClerkName varchar(200),
@Type varchar(200)
AS
BEGIN
DECLARE @AgencyTable TABLE (GpoId int,OfcId int,Postal_Code int,Total_Bills int,Total_Amount BIGINT)
--1. Billing Summary By GPO Name
INSERT @AgencyTable (Postal_Code,OfcId,Total_Bills,Total_Amount)--GpoId,
SELECT bil.GroupId, Bil.SubOfficeId ,isnull(COUNT(Bil.ConsumerNumber),0) --AS Total_Bills
,ISNULL(SUM(Bil.C_Amount),0) - ISNULL(SUM(Bil.Commission),0) --AS Total_Amount
FROM BillTxnSO as Bil inner join pp_offices ofc On bil.GroupId = ofc.Group_Id and bil.SubOfficeId = ofc.OfficeCode and ofc.Postal_Code = bil.PostalCode
Where bil.GroupId = @GroupId
Group by bil.GroupId, Bil.SubOfficeId
--select * from @AgencyTable
SELECT ofc.OfficeName as SubOffice,ofc.Group_ID as GroupID, ofc.Postal_Code as POCode,isnull(gpo.Total_Bills,0)as NoOfBills , isnull(gpo.Total_Amount,0) as Amount
FROM @AgencyTable gpo
INNER JOIN pp_offices ofc On ofc.Group_Id = gpo.GpoId and gpo.OfcId = ofc.OfficeCode and gpo.Postal_Code=ofc.Postal_Code
ORDER BY ofc.OfficeName
END
我尝试过:
我想在应用程序中使用此存储过程显示记录,但这不会显示任何记录
What I have tried:
I want to show record in application using this stored procedure but this do'not show any record
推荐答案
问题在于部分WHERE子句
The problem is with the part of the WHERE clause
AND TransDate BETWEEN @DateFrom AND @DatTo
我怀疑你正在使用G. EDATE()或DateTime.Now();派生@DateFrom参数,时间导致数据超出范围。
有几种方法可以解决这个问题。将参数类型更改为日期
而不是DateTime
I suspect that you are using GEDATE() or DateTime.Now(); to derive the @DateFrom parameter and the Time is causing data to fall outside the range.
There are a couple of ways around this. Either change the parameter types to Date
not DateTime
alter PROCEDURE [dbo].[Proc_RptDailySummaryPTCLBillsCollection_Result]
-- Add the parameters for the stored procedure here
@DateFrom Date,
@DatTo Date, -- ...etc
或者将WHERE子句更改为之间的开头一天@DateFrom和@DateTo结束一天 - 最后一部分与说......和@DateTo之后的一天开始相同 - 可以这样做:
Or change the WHERE clause to read "Between the start of day @DateFrom and the end of the day @DateTo" - the last part is the same as saying "...and the start of the day after @DateTo" - which can be done like this:
AND TransDate BETWEEN dateadd(dd, datediff(dd, 0, @DateFrom), 0) AND dateadd(dd, datediff(dd, 0, @DatTo) + 1, 0)
这篇关于我的存储过程无法在应用程序中显示任何记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!