Using COALESCE to avoid dynamic SQL ?
Posted
by krul
on Stack Overflow
See other posts from Stack Overflow
or by krul
Published on 2010-04-20T17:33:27Z
Indexed on
2010/04/20
18:23 UTC
Read the original article
Hit count: 316
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)
© Stack Overflow or respective owner