Help with where clause in Stored proc and Qry Analyzer getting different rec. counts -- sql server programming

Steve Kass
3/20/2004 8:51:36 PM

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

[quoted text, click to view]

Stacey Howard
3/20/2004 11:38:23 PM

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

[quoted text, click to view]

Steve Kass
3/21/2004 1:22:13 AM

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

[quoted text, click to view]

Steve Kass
3/21/2004 1:29:26 AM

Stacey,

One more thing. A workaround is probably to use RTRIM()
fkarInvFlagRefID in your charindex call, and while it may not matter,
I'd suggest leaving out the third parameter to charindex or changing it
to 1. I don't think 0 is officially allowed as a starting point for
charindex.

Steve

[quoted text, click to view]

Robin
6/10/2004 10:51:02 AM

I am having the same problem as Stacy. When I run a stored procedure, I receive different results (more records) than if I copied the query from the stored procedure and substitute a variable in the query with the actual value that I'm using when passing in the value as a parameter into the stored proc.

In other words, when I execute the following command in SQL Query Analyzer: exec sp_MyStoredProc '6', it returns 34 records

When I run the query within the stored proc in a SQL Query Analyzer window
select name, league_id from softball_teams where region = '6'
unio
select name, league_id from baseball_teams where region = '6
--returns 31 records (This is an example of the type of query, not the actual one

Any idea why the result sets would be different

Thanks

Keith Kratochvil
6/10/2004 1:16:53 PM

The results should be the same. Stored procedures are just compiled sql =
statements. =20

Can you create a script that someone can use to reproduce the problem =
that you are seeing? =20

We would need CREATE TABLE script(s), scripts that we can use to INSERT =
INTO the table, and we will need the CREATE PROC statement.

--=20
Keith

[quoted text, click to view]
I receive different results (more records) than if I copied the query =
from the stored procedure and substitute a variable in the query with =
the actual value that I'm using when passing in the value as a parameter =
into the stored proc.=20
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Help with where clause in Stored proc and Qry Analyzer getting different rec. counts