|
|
<html>
<head>
<link rel="stylesheet" href="style.css">
</head>
<body>
<h1>
"ListProjectsByIDs" Stored Procedure
</h1>
<b>Description:</b>
<p>
This stored procedure retrieves a list of Project summary information from
within a list of possible Project IDs. The records returned are
further narrowed based on the role of the User ID submitted.
</p>
<b>Definition:</b>
<pre>
CREATE PROCEDURE TT_ListProjectsByIDs
(
@ProjectIDs nvarchar(512),
@UserID int
)
AS
DECLARE @sql nvarchar(1024),
@RoleID int
SELECT @RoleID = RoleID
FROM TT_Users
WHERE UserID = @UserID;
IF (@RoleID = 1)
BEGIN
SET @sql = 'SELECT P.ProjectID,'
SET @sql = @sql + ' P.Name AS ProjectName,'
SET @sql = @sql + ' P.EstCompletionDate,'
SET @sql = @sql + ' P.EstDuration AS EstHours,'
SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
SET @sql = @sql + 'FROM TT_Projects P INNER JOIN TT_EntryLog EL'
SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'
END
ELSE IF (@RoleID = 2)
BEGIN
SET @sql = 'SELECT P.ProjectID,'
SET @sql = @sql + ' P.Name AS ProjectName,'
SET @sql = @sql + ' P.EstCompletionDate,'
SET @sql = @sql + ' P.EstDuration AS EstHours,'
SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
SET @sql = @sql + 'FROM TT_Projects P INNER JOIN TT_EntryLog EL'
SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
SET @sql = @sql + ' AND P.ManagerUserID = ' + CAST(@UserID AS nvarchar(20))
SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'
END
ELSE
BEGIN
SET @sql = 'SELECT P.ProjectID,'
SET @sql = @sql + ' P.Name AS ProjectName,'
SET @sql = @sql + ' P.EstCompletionDate,'
SET @sql = @sql + ' P.EstDuration AS EstHours,'
SET @sql = @sql + ' P.EstDuration AS ActualHours '
SET @sql = @sql + 'FROM TT_Projects P WHERE 1=0 '
END
EXEC sp_executesql @sql
</pre>
<b>Database Tables Used: </b>
<p>
The primary key in the Projects table is the ProjectID identity field.
<p>
<img src="../docs/images/1x1.gif" width="25"> <img src="../docs/images/Projects.png"></p>
<p>
The primary key in the EntryLog table is the EntryLogID identity field.
The field ProjectID references back to the Projects table's primary key.
<p>
<img src="../docs/images/1x1.gif" width="25"> <img src="../docs/images/EntryLog.png"></p>
<p></p>
</body>
</html>
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.
A list of licenses authors might use can be found here
Submit an article or tip