SQLTeam.com | Weblogs | Forums

Access Query to Stored Procedure


#1

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;


#2

See below. The only two things I did were

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;

#3

Excellent. Many thanks

Phil