Re: Help with where clause in Stored proc and Qry Analyzer getting different rec. counts
From: Steve Kass (skass_at_drew.edu)
Date: 03/21/04
- Next message: Steve Kass: "Re: Help with where clause in Stored proc and Qry Analyzer getting different rec. counts"
- Previous message: Stacey Howard: "Re: Help with where clause in Stored proc and Qry Analyzer getting different rec. counts"
- In reply to: Stacey Howard: "Re: Help with where clause in Stored proc and Qry Analyzer getting different rec. counts"
- Next in thread: Steve Kass: "Re: Help with where clause in Stored proc and Qry Analyzer getting different rec. counts"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 21 Mar 2004 01:22:13 -0500
Stacey,
The only thing I can think of that would cause this is if the data
type of fkarInvFlagRefID is char(10) or some nullable char (not varchar)
type that holds more than 5 characters, and that the table is somehow
being created with different ANSI_PADDING settings the different times
you run this query. I'm still not sure exactly how that behavior would
come up, if the table isn't ever recreated, and I thought I saw what you
describe happen once when it shouldn't, but I couldn't reproduce it.
If the table doesn't need to be created with ANSI_PADDING OFF but is,
can you try with ANSI_PADDING ON ? Or if fkarInvFlagRefID is
char(something), can you change it to varchar?
It would help if you give an entire repro: the create table statement,
the complete query (what you show is not a query that runs from query
analyzer) and stored procedure and procedure call.
If you are calling the stored procedure from somewhere other than query
analyzer, the particular connection might have different default
settings of ansi_padding, by the way.
Steve
Stacey Howard wrote:
>Thanks for the advice. Can you explain this for me. I'm using this case
>expression for a field fldfkarInvFlagRefID. The field fkarInvFlagRefID has
>the value CHAP7, which is contained in the parmFlag parameter. When I run
>the expression from Qry Analyzer I get 1 in the field
>fldfkarInvFlagRefID. When run from a stored proc, the same code copied from
>Qry Analyzer, I get 2. Any idea why? I really appreciate your help in this
>matter. Stacey :)
>
>declare @parmFlag varchar(200)
>select @parmFlag ='~,~CHAP11~,~CHAP13~,~CHAP7~,~DISPUT~,~No
>Flag~,~PNDLIT~,~TEST~,~UNCOLL~,~'
>
>fldfkarInvFlagRefID = case when charindex('~,~' + fkarInvFlagRefID +
>'~,~',@parmFlag,0) = 0 then 2 else 1 end
>
>
>"Steve Kass" <skass@drew.edu> wrote in message
>news:%23erEkcuDEHA.3080@TK2MSFTNGP10.phx.gbl...
>
>
>>Stacey,
>>
>> You shouldn't be getting different results from Query Analyzer than
>>from a stored procedure. In Query Analyzer, are you running the stored
>>proc, or are you replacing @GLShortPeriod with a value? Maybe you
>>aren't running the exact same query?
>>
>>There are a couple things that look questionable, though.
>>
>>Make sure the OR and ANDs are associated the way you want. The part in
>>parentheses will be interpreted as
>>
>>WHERE
>> (
>> fldfkarInvFlagRefID = 1 and SDate < '200403' AND EDate is null
>> ) or (
>> EDate >= @GLShortPeriod
>> )
>>or ...
>>
>>Is that what you want? It's best to add parentheses so there is no
>>ambiguity.
>>
>>
>>Also, '200403' means April 3, 2020. Is that what you mean?
>>
>>Steve Kass
>>Drew University
>>
>>Stacey Howard wrote:
>>
>>
>>
>>>I'm having an issue with what I believe to be criteria precedence. Here
>>>
>>>
>is
>
>
>>>the WHERE clause of a sql statement that I run in both Query analyzer and
>>>
>>>
>in
>
>
>>>a stored proc. The are the same syntactically. I get different record
>>>
>>>
>counts
>
>
>>>in my result sets. Can anyone shed some light as to what is wrong with
>>>
>>>
>this
>
>
>>>clause.
>>>
>>>
>>>WHERE (fldfkarInvFlagRefID = 1 AND SDate <'200403' AND EDate is null or
>>>EDate >=@GLShortPeriod)
>>> OR SDate < '200403' AND EDate <= '200403' AND fldpkarInvFlagID = 1
>>> OR SDate > @GLShortPeriod AND fldpkarInvFlagID = 1
>>> OR fldpkarInvFlagID2 = 1
>>>
>>>
>>>
>>>
>>>
>>>
>
>
>
>
- Next message: Steve Kass: "Re: Help with where clause in Stored proc and Qry Analyzer getting different rec. counts"
- Previous message: Stacey Howard: "Re: Help with where clause in Stored proc and Qry Analyzer getting different rec. counts"
- In reply to: Stacey Howard: "Re: Help with where clause in Stored proc and Qry Analyzer getting different rec. counts"
- Next in thread: Steve Kass: "Re: Help with where clause in Stored proc and Qry Analyzer getting different rec. counts"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
