I'd argue that the design is flawed

The intention is to query data on 1 or both dates - so give the sproc the...

It doesn't compile because of this:

LEFT OUTER JOIN

  (SELECT COUNT(ID) AS NumberDue

   FROM tblMEMResellerOrders ResOrds

   WHERE (resords.Expiration <= '12/30/2005 23:59:59')

    AND (resords.Expiration >= '12/1/2005 00:00:00')

    AND (resords.PurchaseOption <> 'NFR')

    AND (resords.Cancelled = 0))...

Check BOL again under the CONVERT function. There are no direct convert formats that give you this exact format, so you need to concatenate format 101 (mm/dd/yyyy) and format 108...

I have a similar, but slightly smaller data set that I was able to test with. An Account table (500K rows) and transactions on each account (5 million rows). Selecting a...

>>getting the validation criteria upfront and passing all 12 into the function as parameters?

Yep, exactly.

Another consideration is the type of database application. Is it OLTP ? Is it a data warehouse ? Dimensionally modeled ?

Consider dimensionally modeled warehouses or marts - you have a fact...

Are you sure the error is coming from the SET statement, and not from the subsequent SQL ?

http://support.microsoft.com/default.aspx?scid=kb;en-us;819264

What happens inside fn_Validate() ?

Does it just use the passed in values, or does it perform additional querying on other tables ? If there is additional querying, you will still...

Post the DDL of the table, or at least tell us what the primary key is, and we'll show you how to join to the inserted virtual table to update...

Ask anyone who has worked on an app, like older versions of Siebel CRM for example, where there is no RI on 2000+ tables and ask them how well the...

You need to provide the business rules for which address to select.

If there are multiple addresses per person, and you want some or all of the address columns in the...

We also need to see the query execution plan. Put this before the SQL and post the results:

Set Showplan_text on

go

>>The query is a join without further filtering, which means you'll...

What are the indexes on the 2 tables ?

Then that's the column you should join on, instead of Vendor:

where JackInTheBox.cardnbr = t1.cardnbr

Does your table have a Primary Key ?