I don't think you have any althernative other than dynamic SQL.

SQL code is parsed. Then it is executed. All code is parsed, including both branches of an IF .. THEN...

>>month(datecolumn)

The MONTH() function only takes true datetime types.

A string value in DDMMM format won't implicitly convert to a datetime.

Since it appears as if year is irrelevant in your case, simply...

Have you considered global temp tables (with "##" prefix) ? They can be accessed across SPIDs.

If you are doing "SELECT *" into a #temp table, all the column names need to be unique.

In this case both your tables contain a column named "caseID", therefore temp...

What about ISDATE() ?

Idenitify the bad data:

SELECT *

FROM YourTable

WHERE ISDATE(EffDt) = 0

There are T-SQL people who know & use the SQL built in functions.

And then, there are "The Others" ...

Code below called many times per minute on 1 of my servers....

SELECT Count(*)

FROM TSQLCoders

WHERE FamiliarWithLEN = 1

AND FamiliarWithDATALENGTH = 0

Result:

Too bloody many

>>The application does not access any other database and I am able to run another package using the same server so that rules out permissions.

Not necessarily.

When you run this...

>>tblItem is a recursive table where the parent ID refers to the ID of the parent item in the table itself.

If something is recursive, you need to join it to...

Going back to your original post:

>>The maintenance plan remains the same regardless of the execution time. The maintenance plan doesnt use table scans only index seeks.

Just to confirm, you mean...

Can you give us some more details about the function dbo.CallSearchL3_2(). What does it do internally ? Does it use tempdb ?

Does you system have any tables pinned in memory...

Your DBCC results are interesting, because they appear to be the opposite of what I was expecting.

Since you stated that the execution plan remained the same, yet execution times got...

It appears that using an Oracle Sequence like this won't work.

2 options:

1. Create a stored procedure on Oracle and pass it the parameters for INSERT. The stored proc can get...

If the exec plans are identical then ...

Run DBCC SHOWCONTIG() on the 3 tables at the start of the day. Keep the results.

When the slowness is noticed run DBCC SHOWCONTIG()...

The index scan on table [Price] is likely to be expensive with 14m rows.

Ideally you'd want an index seek.

Can you create additional indices on these tables ?

Also, I...