Recent post of same topic:

http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=13248&FORUM_ID=49&CAT_ID=1&Topic_Title=Handling+%22optional+fields%22&Forum_Title=Strategies

Here's an alternative:

IF EXISTS

(

SELECT * 

FROM Clients C

INNER JOIN InvoiceHeader I 

  ON I.ClientID=C.ClientID 

INNER JOIN Addresses A 

  ON A.ClientID=C.ClientID

WHERE I.InvoiceNr=@InvoiceNr 

AND A.PrimaryAddr=1

)

BEGIN

SELECT...

OK, try this:

SET NOCOUNT ON

SELECT COALESCE(LTRIM(COALESCE(Salutation,'') + ' ' + FName + ' ' + LName), BusinessName) AS ClientName, A.* FROM Clients C

INNER JOIN InvoiceHeader I...

Should it return the Primary Address even if there are billing address(es)?

I'm not quite sure what you mean by get the join to stop...could you elaborate?

I've been outdone once again. 🙂

As a final note, you may want to go into Enterprise Manager and change the table in the Design View, then click Save Script button and see what SQL internally...

I get the same error, can't find in BOL why the syntax error. Anyway, what about this:

-- First add an identity column...

ALTER TABLE MyTable

ADD MyNewIdentityColumn...

quote:

---

Are the multiple creates faster than the IF ELSE IF approach?

---

Yes, for each PROCEDURE in...

Hmmm, looks like there is a much easier solution to this one (thanks to my illustrious colleague, RonKyle...):

Look up Books On Line for "SET IDENTITY_INSERT"

You can turn identity on and...

quote:

---

I try'd that but whatif you want to switch it back on?

---

You'd note where the...

I would go with Antares approach: it's modular, maintainable, and has the best chance for a well-optimized plan based on the SARGs.

One minor thing, you could possible combine: