Using COALESCE to avoid dynamic SQL ?
- by krul
I would like to use following sql to avoid constructing sql dynamically:
SELECT CommentID, Comment,
FROM Comments
--if Author id is not null then filter upon author id otherwise get all comments (ignore author id)
WHERE AuthorID LIKE COALESCE(@AuthorId, '%')
--if comment type is present filter upon it, otherwise get all comments (ignore comment type condition)
AND CommentType LIKE COALESCE(@CommentType, '%')
I want to know is that safe way to approach this problem?
EDIT:
Here is final code that satisfy my need to ignore search parameter if is null and applied it if is present:
SELECT CommentID, Comment,
FROM Comments
--if @AuthorId is not null then filter upon @AuthorId otherwise get all comments (ignore author id)
WHERE AuthorID = COALESCE(@AuthorId, AuthorID)
--if @CommentType is present filter upon it, otherwise get all comments (ignore comment type condition)
AND CommentType = COALESCE(@CommentType, CommentType)
