I really think you're going to drive people crazy trying to follow this because no one else does it.

Almost universal is to use NULL to mean "no value was passed...

takilroy (7/17/2012)

---

Unfortunately, I can't use look up tables because a default value for a parameter MUST be a constant, meaning either a literal value or a @@ constant. So,...

Again, in theory yes.

But in the real world that's often difficult to do and takes a lot of time. And often a change later to the code by someone...

You can use a lookup table to store default/control values.

Or, less overhead but more obscure, you can store the default/control values in specific byte positions of CONTEXT_INFO.

I'm not familiar with MONTHS_BETWEEN, but IF it does always use 31 days as a "month" in that function, then you can get the same result from SQL Server using:

SELECT...

Michael Valentine Jones (7/16/2012)

---

ScottPletcher (7/16/2012)

---

SQLKnowItAll (7/16/2012)

---

ScottPletcher (7/16/2012)

---

CREATE...

SQLKnowItAll (7/16/2012)

---

ScottPletcher (7/16/2012)

---

CREATE TRIGGER dbo.project_trg_ins

ON dbo.project

AFTER INSERT

AS

UPDATE...

Something like below should do it:

SELECT

CASE WHEN backup_month IS NULL THEN 'ALL'

ELSE LEFT(DATENAME(MONTH, backup_month), 3) END AS...

CREATE TRIGGER dbo.project_trg_ins

ON dbo.project

AFTER INSERT

AS

UPDATE p

SET

...

JayWinter (7/12/2012)

---

In this case SKU and UPC are the same and therefore its usage is correct. However my challenge is that there may be more than 1 record that meets...

Presumably the ContactID is indexed.

If there can only be single-line gaps, you can do this:

SELECT

c1.ContactID - 1 AS Missing_Number

FROM #Contact c1

WHERE NOT EXISTS...

dan-572483

I was surprised to see that the Estimated Execution Plan shows a slightly higher cost for LEFT(LastName,2) over LIKE. The Plan for LIKE recommended creating a nonclustered index...

Not quite.

LIKE 'xxx%' can...

I don't think you really need indexing, since you can do everything in a single pass of the table, w/o JOINs, etc..

SELECT

ACTIONID,

CASE...

Lynn Pettis (7/11/2012)

---

Matt Miller (#4) (7/11/2012)

---

Lynn Pettis (7/11/2012)

---

This works as well:

CREATE TABLE [dbo].[TEST1](

[ID] [VARCHAR](10) NULL,

[VALUE] [int] NULL

);

GO

INSERT INTO dbo.TEST1 (ID, VALUE) SELECT 1, 10;

SELECT 'PRE' LABEL1, * FROM dbo.TEST1;

DECLARE @SAVE1...