My stored procedure cannot show any record in application

Question

0.00/5 (No votes)

See more:

, +

SQL

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

Posted 19-Apr-16 23:54pm

Member 12138525

Updated 24-Apr-16 20:24pm

v3

Add a Solution

Comments

Sinisa Hajnal 20-Apr-16 6:09am

Execute it in Query analyzer and check the result. Do you get anything? If no, debug the procedure. If yes, check the parameters you're sending from the code. If they are OK then debug the code calling the procedure.

jaket-cp 25-Apr-16 4:31am

Query analyzer - that is old school :)
It is a bit more of a mouth full now - Microsoft SQL Server Management Studio (or SSMS for short)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2016-04-20T01:08:00

The problem is with the part of the WHERE clause

SQL

AND TransDate  BETWEEN @DateFrom AND @DatTo

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

SQL

alter PROCEDURE [dbo].[Proc_RptDailySummaryPTCLBillsCollection_Result] 
 
	-- Add the parameters for the stored procedure here

	@DateFrom Date,
	@DatTo Date, -- ...etc

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:

SQL

AND TransDate  BETWEEN dateadd(dd, datediff(dd, 0, @DateFrom), 0) AND dateadd(dd, datediff(dd, 0, @DatTo) + 1, 0)