到目前为止,这是我的存储过程
ALTER PROC [dbo].[getItems]
(
@EventId int,
@OrgUserId int
) AS
BEGIN
DECLARE @BoardBookId int;
SET @BoardBookId = (SELECT bb.BoardbookId FROM Boardbook bb WHERE bb.ItemId = @EventId);
WITH PermissionDeniedAttachments AS
(
SELECT bbi.CategoryItemId
FROM BoardbookItem bbi
INNER JOIN CategoryItem ci ON ci.CategoryItemId = bbi.CategoryItemId
WHERE bbi.BoardbookId = @BoardBookId AND bbi.CategoryItemId > 0 AND ci.catID NOT IN (SELECT catID from dbo.f_Categories(@OrgUserId))
)
SELECT
bbi.BoardbookId,
bbi.BoardbookItemId,
bbi.CategoryItemId,
bbi.ChBy,
bbi.ChDt,
bbi.CrBy,
bbi.CrDt,
bbi.DeletedBy,
bbi.DeletedDt,
bbi.Description,
bbi.Duration,
bbi.ParentBoardbookItemId,
bbi.SortOrder,
bbi.Title,
bbi.Resolutions,
bbi.Presenter,
bbi.BoardbookItemType,
u1.Name_FirstLast as [ItemCreatedByName],
u2.Name_FirstLast as [ItemChangedByName],
CAST(CASE WHEN ci.deletedDt IS NULL THEN 0
ELSE 1 END AS BIT) AS Deleted,
i.itemType as [DocType],
ISNULL(ci.catID, 0) AS CatId,
ISNULL(ci.itemID, 0) AS ItemId,
d.docID as docId,
d.docPages as DocPages,
d.docPwdProtected AS HasPassword,
bbi.BoardbookItemTypeCustom
from BoardbookItem bbi
LEFT JOIN Users u1 on u1.UserName=bbi.CrBy
LEFT JOIN Users u2 on u2.UserName=bbi.ChBy
LEFT OUTER JOIN CategoryItem ci ON bbi.CategoryItemId IS NOT NULL AND bbi.CategoryItemId = ci.CategoryItemId
LEFT JOIN Item i ON ci.itemID = i.itemID
LEFT JOIN Documents d on d.docID = bbi.DocId
where bbi.BoardbookId=@BoardBookId AND bbi.DeletedDt IS NULL AND
(bbi.CategoryItemId IS NULL OR bbi.CategoryItemId NOT IN (Select CategoryItemId from PermissionDeniedAttachments))
现在我想要的是放入一个
var @submenuitem bit
并对其进行测试...因此,如果
@submenuitem = 0
作为响应,我将获得与现在一样的效果,但是如果
@submenuitem = 1
我想在“where”中再检查一次。'AND bbi.ParentBoardbookItemId='0'
看来可以用
IF ELSE
语句完成,但不幸的是我做不到正确的方法先感谢您!
最佳答案
我建议对您的WHERE
子句进行以下简单更改:
LEFT JOIN Documents d on d.docID = bbi.DocId
where bbi.BoardbookId=@BoardBookId AND bbi.DeletedDt IS NULL AND
(bbi.CategoryItemId IS NULL OR bbi.CategoryItemId NOT IN (Select CategoryItemId from PermissionDeniedAttachments)
AND (@submenuitem = 0 OR bbi.ParentBoardbookItemId='0')