As I said before, we KNOW it is has a valid value before the cursor begins looping.

I tried the = 0 .vs <> -1 but will do so again.

Thanks, I've got it now. Yes, all columns are required and it's easier to write. 😉

Now, based on your post, I have added all the parameters for my Sp and am getting an error:

The parameterized query '(@Payment_ID as int,

...

I'm sorry for the confusion about edit capabilities. I *am* allowed to make changes and it looks like this *breakthrough* is going to be a big feather in my cap...

Say, Gail, I got this from Mark Willis on Experts-Exchange. Basically it's building dynamic SQL.

ALTER PROCEDURE [dbo].[Select_Payments]

@PaymentID as int,

...

Attached is the table creation script, one record of table data and a query along with it's execution plan.

GilaMonster (7/24/2008)

Since there's no changing that, ignore my comments. Maybe in the future if the system hits performance problems you can be the hero by telling them a possible cause....

Any more tips on optimizing SP's where the WHERE clause is formatted like that?

This...

I'm really kind of a newbie. The SP was generated by a tool that is used here and I am only modifying it to try to make it more efficient...

if I include both dates, it returns all rows instead of only records in the range

if I set the end date as null, it returns all rows.

There are 946000 records...

Would this work correctly?

AND (((@PaymentDtTmStart IS NULL)

AND (@PaymentDtTmEnd IS NULL))

...

HUH? I just need to know how to check the ranges.

So, do I look funny with egg on my face? Thanks a lot, you just saved my behind!

BTW, because of the way things are being run, I cannot use a DECLARE or IF, so...

Here is the final working code:

SELECT CASE

WHEN EXISTS(SELECT 1 FROM...