我的存储过程无法在应用程序中显示任何记录 [英] 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屋！