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: 293

Filed under:
|
|

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

Related posts about sql

Related posts about best-practice