Look at CURSOR_STATUS in BOL

A minor mod to Jeff's code fixes it

WITH

cteGroups AS

(

SELECT dt,

cat,

Sequence = ROW_NUMBER()...

WITH CTE AS (

SELECT [tradeDateTime],

CASE WHEN [profit] >=0 THEN 'W' ELSE 'L' END AS WinLoss,

...

Not very efficient and SQL Server 2005/2008 only

WITH CTE AS (

SELECT c1,

c2,

c3,

...

SELECT x.page.value('(PageProperties/PageTitle)[1]', 'varchar(100)') AS PageTitle,

x.page.query('.') AS Page

from @xml.nodes('//Pages/Page') AS x (page)

Here's a hacky way of doing it...

declare @sample table (id int identity (0,1), fruit varchar(20), meat varchar(20))

insert into @sample

select null, null union all

select null, 'Beef' union all

select 'Apples', 'Chicken' union...

WayneS (10/29/2010)

---

Craig Farrell (10/28/2010)

---

Think this would be a bit much as my signature?

CREATE TRIGGER trg_Celko ON vw_SSC_PostList FOR AFTER INSERT

AS

-- Go read the documents, inline comments are for sissies.

IF EXISTS...

DECLARE @t TABLE(Id INT, ParentID INT, Caption VARCHAR(10))

INSERT INTO @t(Id, ParentID, Caption)

SELECT 1, 0, 'Caption1' UNION ALL

SELECT 2, 1, 'Caption2' UNION ALL

SELECT 3, 2, 'Caption3';

WITH CTE AS (

SELECT Id,...

Try these

SET @FieldName = @MessageBody.value('local-name((//Message/_x0023_ins/*[position()=sql:variable("@LoopCount")])[1])', 'varchar(128)')

SET @FieldName = @MessageBody.value('data((//Message/_x0023_ins/*[position()=sql:variable("@LoopCount")])[1])', 'varchar(128)')

You were really close

SELECT

V.y.value('./text()[1]','int') as ID

FROM @xmlRoleConsumerTemp.nodes('/RoleConsumerTemp/ID') AS V(y)

WITH CTE AS (

SELECT Pupil_ID,

AttendanceID,

ROW_NUMBER() OVER(PARTITION BY Pupil_ID ORDER BY ATT_PAT_FROM_DATE,ATT_PAT_TO_DATE,AttendanceID) AS rn

FROM #Test1)

SELECT Pupil_ID,

...

I think this give the correct results, but I'm not too clear on how to handle multiple test_payments rows with non-null write_off_codes for a single account.

with charges as (

select ta.account,...

Try using EXCEPT

INSERT INTO contacts (email, business)

SELECT email, business

FROM scrapes

WHERE (dbo.vaValidEmail(email) = 1)

EXCEPT

SELECT email, business

FROM contacts;

Not very efficient, but should give the correct results

SELECT s1.MemberID,

s1.EffDate,

MIN(t1.ExpDate) AS ExpDate

FROM membership s1...

WITH CTE AS (

SELECT Antibiotics,Organism, value,

ROW_NUMBER() OVER(PARTITION BY Organism,value ORDER BY SortOrder) AS rn

FROM MyTable)

SELECT Organism,

MAX(CASE...