After having spent too long developing an Access app, I need to move to SQL 2008. I have searched and read and I am still struggling. To get me on the right path, can someone covert this to a Stored Procedure that will run on SQL 2008 ?
I expect the code is far from neat, so appreciate any pointers. Thanks.
SELECT R.ID,
(Select iiF(Count(*)>0,TRUE,FALSE) From [Risks and Issues Attachments] Att Where Att.Parent = [R].ID) AS Attachment,
R.Title,
Workstreams.Workstream,
iif(Plans.System = True,Plans.Title,Plans.Title & ' [' & Plans.Start & ']') AS [Milestone Impacted],
R.[Next Review],
R.[Due By] AS Deadline,
R.[Risk Level] AS [Issue Level],
R.Status
FROM (Workstreams INNER JOIN Plans ON Workstreams.ID = Plans.WorkstreamID) INNER JOIN [Risks and Issues] AS R ON Plans.ID = R.PlanID
WHERE R.[RAID Type] = 'Issue' and [Project Reference] = [@PROJECT]
ORDER BY R.Status DESC , R.[Impact Score] DESC;
a) change the IIF to CASE expressions. IIF is available on on SQL Server 2012 or later
b) Change the logical expressions TRUE and FALSE to 1 and 0. SQL Server interprets 1 as TRUE and 0 as FALSE.
You may need to do other changes - for example, if Plans.Start is not a character type (e.g., if it is datetime type) you will need to cast it to a character type etc. So this is something to get you started, not the solution you want to get to.
SELECT R.ID ,
( SELECT CASE WHEN Count(*)>0 THEN 1 ELSE 0 END
FROM [Risks and Issues Attachments] Att
WHERE Att.Parent = [R].ID
) AS Attachment ,
R.Title ,
Workstreams.Workstream ,
CASE
WHEN Plans.System = 1 THEN Plans.Title
ELSE Plans.Title + ' [' + Plans.Start + ']'
END AS [Milestone Impacted] ,
R.[Next Review] ,
R.[Due By] AS Deadline ,
R.[Risk Level] AS [Issue Level] ,
R.Status
FROM Workstreams
INNER JOIN Plans ON Workstreams.ID = Plans.WorkstreamID
INNER JOIN [Risks and Issues] AS R ON Plans.ID = R.PlanID
WHERE R.[RAID Type] = 'Issue'
AND [Project Reference] = @PROJECT
ORDER BY R.Status DESC ,
R.[Impact Score] DESC;