DECLARE @ResultTabe table(CommentID int)
exec('INSERT INTO @ResultTabe (CommentID )
SELECT DISTINCT c.ID
FROM Comment as c
INNER JOIN CommentClass cc on c.ClassID=cc.ID
AND ('+@DepartmentID +'IS NULL OR cc.DepartmentID in ('+@DepartmentID+'))
AND ('+@ClassID +' IS NULL OR cc.ID in ('+@ClassID+'))
WHERE
('+@CurrentUserID +'IS NULL OR c.CreatedByUserID='+@CurrentUserID+')
AND ( c.IsDeleted=0)
AND ( c.Visible=1)') --必须公开外部才可见
------解决思路----------------------
DECLARE @ResultTabe table(CommentID int)
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT DISTINCT c.ID
FROM Comment as c
INNER JOIN CommentClass cc on c.ClassID=cc.ID'
SET @SQL=@SQL+ISNULL(' AND cc.DepartmentID in ('+CAST(@DepartmentID AS VARCHAR)+')','')
SET @SQL=@SQL+ISNULL(' AND cc.ID in ('+CAST(@ClassID AS VARCHAR)+')','')
SET @SQL=@SQL+' WHERE '
SET @SQL=@SQL+ISNULL(' c.CreatedByUserID in ('+CAST(@CurrentUserID AS VARCHAR)+') AND ','')
SET @SQL=@SQL+' c.IsDeleted=0 AND c.Visible=1'
INSERT INTO @ResultTabe (CommentID )
EXEC(@SQL)