Please post table def and sample data in a ready to use format as described in the first link in my signature.

Also, please include your expected result based on the...

What table would a value for DateColumn ='20100101' get inserted? 😉

Did you try

WHERE A.CashPurse1 < > 0

Just as a side note: instead of

WHERE DATEPART(YEAR,DateColumn)=2009

I'd rather recommend use

WHERE DateColumn>='20090101' AND DateColumny'20100101'

The original version won't benefit from a (hopefully) existing index on DateColumn.

It's hard to troubleshoot without having the table defs referenced by the sproc.

You might need to run the code with defiined parameter instead of calling the sproc with input variables....

I think there's a little more to change...

Change the block before the comment -- Execute SQL statement

SET @sqlStatement = @sqlStatement + @whereSql + '

)

SELECT * FROM PaymentViewPaged

WHERE RowIndex between @PageIndex...

@ninja: I edited the quote range in my reply. You might need to adjust yours to reflect that, too 😀

I just removed the GROUP BY as well as the CONVERT() function. The result is still identical:

SELECTDISTINCT OM.ID,Sequence

FROM #DataHistory OM

OUTER APPLY

(

SELECT

STUFF((

SELECT ',' + LTRIM(RTRIM(O.Letter ))

FROM #DataHistory...

Matt Miller (#4) (7/14/2011)

---

According to that - since you're dynmiacally building the query, so definitely don't need the function in the JOIN. Instead - use the parameter differently:

SELECT *...

Start with comparing the actual execution plans if both are exactly the same.

krypto69 (7/14/2011)

---

yep I talked to the dev again and he said it was a dynamic parameter..so they...

Almost impossible to answer based on the information provided so far.

Parameter sniffing? Out of date statistics? Missing/dropped indexes? Changed data volume? Heavy server load? Disc issues? ...

Please remember: neither do...

It depends.

What do you expect to be a valid result...

run the inner query separately:

SELECT wrhid

FROM (

(SELECT wrkID, MAX(wrhid) as wrhid

FROM tblWerknemerHistorie

WHERE wrkID in (SELECT wrh.wrkid

FROM dbTR_20110708.dbo.vwWerknemers WRK

INNER JOIN dbTR_20110708.dbo.tblWerknemerHistorie WRH on...