Link to home
Start Free TrialLog in
Avatar of Kevin Hill
Kevin HillFlag for United States of America

asked on

Results missing from stored proc

ok...I must be blind.

I have this procedure (which I didn't write):

CREATE  PROCEDURE  dbo.spWebqryAccountList41257
     (@StartDate nvarchar(15)
     ,@EndDate nvarchar(15)
     ,@ClientID int
     ,@ArpCode nvarchar(15)
     ,@brchid nvarchar(5)
     ,@username nvarchar(10)
     )  
AS

SELECT       *
FROM      
     dbo.vwWebClaimDetailNext4
WHERE      
     ([Date Handled] between  @StartDate AND @EndDate)
      AND   ([Client ID] = @ClientID)
      and ARPCODE in (@ArpCode)
      and BranchID = @brchid
      and username = @username


Note that all it is doing is selecting from a view and narrowing the results

I have two executions:

EXEC spWebqryAccountList41257
     @StartDate = '2/1/2006',
     @EndDate = '3/3/2006',
     @ClientID = 2287,
     @ARPCode = 'A',
     @BrchID = 'A#79',
     @Username = 'ME2287'

Result = 4 records

EXEC spWebqryAccountList41257
     @StartDate = '2/1/2006',
     @EndDate = '3/3/2006',
     @ClientID = 2287,
     @ARPCode = 'A',
     @BrchID = 'Mil#104',
     @Username = 'ME2287'

Result = 0 records

The only difference between the two is the @BrchID parameter.

The underlying view run with the same parameters as the second execution in the WHERE clause returns records (7, to be exact) for the second set, but the stored proc does not.

If I do this:
SELECT       *
FROM      
     dbo.vwWebClaimDetailNext4
WHERE      
     ([Date Handled]   between '2/1/2006' and '3/3/2006')
      AND   ([Client ID] =2287)
      and ARPCODE in ('A')
      and BranchID = 'Mil#104'
      and username = 'ME2287'

I get the right results....

I've been looking at the # sign as the culprit, but both branches have one (as do many others).  

Help?
ASKER CERTIFIED SOLUTION
Avatar of ewilde
ewilde

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Kevin Hill

ASKER

You sir, are a genius.

You get all the points, and I'm going to leave it open till tomorrow just so everyone else can see how fast you resolved this for me.

I knew it was simple...just couldn't see it.

Thanks,
Kevin3NF
Avatar of ewilde
ewilde

not a genius, just experienced...