Kevin Hill
asked on
Results missing from stored proc
ok...I must be blind.
I have this procedure (which I didn't write):
CREATE PROCEDURE dbo.spWebqryAccountList412 57
(@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?
I have this procedure (which I didn't write):
CREATE PROCEDURE dbo.spWebqryAccountList412
(@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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
not a genius, just experienced...
ASKER
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