Look, your code does enter the next (second) "try" block! But why you expect "error, and display the "Round 2" select"? There is no error to catch in this block...

I need to clarify :

elutin (6/2/2010)

---

This one, does return proper results, However, on the given dataset sample, it performs 3 scans & 26 logical reads (incl. 24 reads of CTE...

This one, does return proper results, However, on the given dataset sample, it performs 3 scans & 26 logical reads (incl. 24 reads of CTE worktable) vs 34 scans but...

Your code behaves exactly as it is written.

It returns only one error as it is one error happens.

Do you expect your code to stop after the firts error?

Then you...

Instead of table structure, could you please provide CREATE TABLE and test DATA population scripts to represent your question in a way that would help your potential helpers. Something like

CREATE...

Unfortunately, your version will not work for situations where the price returns back to what it...

Bhuvnesh (6/2/2010)

---

elutin (6/2/2010)

---

As promised, another version. It contains much more code, but it's probably the fastest way to do what you require:

...

,@Product...

As promised, another version. It contains much more code, but it's probably the fastest way to do what you require:

--We need to copy the data from the master table to...

I guess, using "quirky" update method will give better performance. I cannot show it today, but will do it tomorrow

It's not really a function as such....

Sorry, you need small modification in it:

SELECT id, EGType, Dates

FROM dbo.Table_1

WHERE (@Package IS NULL OR EGTYPE = @Package)

AND (@Date IS NULL OR...

After looking into use of CROSS & OUTER APPLY (my curiosity was influenced by Paul White NZ who suggested more efficient solution for one of the last topics...)

I can suggest...

What about if the price changes back to the same value as it was before:

INSERT INTO #Temp (Name ,Price ,Date)

...

If I'm guessing right, you're writing a stored proc.

To avoid parameter sniffing (which happen in this kind of stored proc) copy values from stored proc input parameters into local variables...

you can use this:

DECLARE @Package char (50)

DECLARE @Date smalldatetime

DECLARE @nulldates bit

-------------------

SET @Package = '**SELECT ALL**'

--SET @Package = 'A'

-------------------

SET @Date = '2010-05-01 00:00:00'...