Hi,

Is it possible to do this without using a stored procedure or alot of custom code...

ALTER PROCEDURE [dbo].[sp_GetTopLifeNStuffCategory]

@Date as datetime

AS

SELECT

c.LifeNStuffCategoryID,
Title,
Ordinal,
LifeNStuffPriorityID,
CASE WHEN startdate IS NULL THEN NULL ELSE 1 END AS 'IsPriortiy'

FROM

tbl_lifenstuff_category c

LEFT JOIN

tbl_Lifenstuffpriority p

ON

c.LifeNStuffCategoryID = p.LifeNStuffCategoryID
AND
p.startdate <= @Date AND p.enddate > @Date

ORDER BY

IsPriortiy DESC,
ordinal

Its the 'case' statement that's the problem.

Cheers, Ian.

I think you pasted the wrong proc, this one doesn't have a case statement

Well wrong version. I added it back in.

Ian wrote:

Well wrong version. I added it back in.

Ok. Yes you can do this, but not with CASE, you can use a DbFunctionCall expression (v2.0) and call COALESCE() to make the case logic possible.

I'm not sure COALESCE will work here. That function returns the first non-null value. I want null if the value is null or 1 otherwise.

(Its so that the first sort field partitions the results into the rows that have joined with the right table followed by those that haven't. )

Have you considered using client-side sorting?

Yup. But I'm happy to leave it as a stored procedure.