How to troubleshoot a strange result from a stored procedure?

I am trying to tune a stored procedure that is long and running slow. As
part of testing, the results are odd from what I am expecting. The testing
is running on 2 databases which restored from the same backup with one
database set with read-only option. This will allows me to check the
original code with modified code. These databases are on my server;
therefore, it is lock down.
The stored procedure has 15 pass-in variables with default values are NULL.
If I modify the stored procedure by adding a non-essential statement such as
SELECT 1 or SELECT getdate() anywhere in the stored procedure, the procedure
return different result.
Test query: exec GetValues @.FName = 'Smith%', @.FName = default, @.Price =
default, @.CustType = default
Part of the existing stored procedure:
if (@.Price is not null or @.Price <> '') and (@.CustomerType is not null or
@.CustType <> '')
select * from CustTable
where @.LastName is null or customer.FName like rtrim(@.LastName)
else
select 'No data'
When I run this on the read-only database, it returns some rows. But when I
run this on the test database with modified code, it returns no data. The
stored procedures from both database are basically same with exception of
adding a non-essential statement (SELECT 1 or SELECT getdate()). Based on
what test query, it should return no data on both databases, but the results
are different.
Is there a tool or methods to figure out the strange results from this
stored procedure? I tested with different methods but come out empty-handed
.
Please help! Thanks!Correction to part of the stored procedure:
if (@.Price is not null or @.Price <> '') and (@.CustomerType is not null or
@.CustType <> '')
select * from CustTable
where @.LastName is null or LName like rtrim(@.LastName)
else
select 'No data'
"KTN" wrote:

> I am trying to tune a stored procedure that is long and running slow. As
> part of testing, the results are odd from what I am expecting. The testin
g
> is running on 2 databases which restored from the same backup with one
> database set with read-only option. This will allows me to check the
> original code with modified code. These databases are on my server;
> therefore, it is lock down.
> The stored procedure has 15 pass-in variables with default values are NULL
.
> If I modify the stored procedure by adding a non-essential statement such
as
> SELECT 1 or SELECT getdate() anywhere in the stored procedure, the procedu
re
> return different result.
>
> Test query: exec GetValues @.FName = 'Smith%', @.FName = default, @.Price =
> default, @.CustType = default
> Part of the existing stored procedure:
> if (@.Price is not null or @.Price <> '') and (@.CustomerType is not null or
> @.CustType <> '')
> select * from CustTable
> where @.LastName is null or customer.FName like rtrim(@.LastName)
> else
> select 'No data'
>
> When I run this on the read-only database, it returns some rows. But when
I
> run this on the test database with modified code, it returns no data. The
> stored procedures from both database are basically same with exception of
> adding a non-essential statement (SELECT 1 or SELECT getdate()). Based on
> what test query, it should return no data on both databases, but the resul
ts
> are different.
> Is there a tool or methods to figure out the strange results from this
> stored procedure? I tested with different methods but come out empty-hand
ed.
> Please help! Thanks!|||KTN
Do you investigate how to optimize the SP or why it returns wrong result?
Have you look into an execution plan? Was the optimizer available to use
indexes? How much data do you return?
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:47DD3664-5829-40E5-92B8-A5EE1E8D9C45@.microsoft.com...
>I am trying to tune a stored procedure that is long and running slow. As
> part of testing, the results are odd from what I am expecting. The
> testing
> is running on 2 databases which restored from the same backup with one
> database set with read-only option. This will allows me to check the
> original code with modified code. These databases are on my server;
> therefore, it is lock down.
> The stored procedure has 15 pass-in variables with default values are
> NULL.
> If I modify the stored procedure by adding a non-essential statement such
> as
> SELECT 1 or SELECT getdate() anywhere in the stored procedure, the
> procedure
> return different result.
>
> Test query: exec GetValues @.FName = 'Smith%', @.FName = default, @.Price =
> default, @.CustType = default
> Part of the existing stored procedure:
> if (@.Price is not null or @.Price <> '') and (@.CustomerType is not null or
> @.CustType <> '')
> select * from CustTable
> where @.LastName is null or customer.FName like rtrim(@.LastName)
> else
> select 'No data'
>
> When I run this on the read-only database, it returns some rows. But when
> I
> run this on the test database with modified code, it returns no data. The
> stored procedures from both database are basically same with exception of
> adding a non-essential statement (SELECT 1 or SELECT getdate()). Based on
> what test query, it should return no data on both databases, but the
> results
> are different.
> Is there a tool or methods to figure out the strange results from this
> stored procedure? I tested with different methods but come out
> empty-handed.
> Please help! Thanks!|||My plan is to optimize the SP and in part of doing that, I found wrong
results with adding non-essential statements such as SELECT getddate()
I looked at the execution plan for the new code is much better via using
some covering index and better query plans.
The data return is very based on the 15 pass-in variables, but mostly under
100 rows.
"Uri Dimant" wrote:

> KTN
> Do you investigate how to optimize the SP or why it returns wrong result?
> Have you look into an execution plan? Was the optimizer available to use
> indexes? How much data do you return?
>
>
>
> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> news:47DD3664-5829-40E5-92B8-A5EE1E8D9C45@.microsoft.com...
>
>