Using a LIKE query in an Oracle procedure

I had two parameters that could be passed to a procedure. Only one was being passed into the procedure – business layer code makes sure of that – so if the pId parameter is present AND it is a match return the record. But I also needed to return the results if the pName parameter was filled out, and do a LIKE query on that field. And since half the reason for this blog is so when I forget how to do it, I can find it again:

PROCEDURE Search(pName IN VARCHAR, pId IN NUMBER, p_cursor OUT sys_refcursor) IS

   BEGIN
       OPEN p_cursor FOR
           SELECT ID, Name
           FROM users 
           WHERE ((pId IS NOT NULL) AND (ID = pId))
           OR ((pName IS NOT NULL) AND (Name LIKE '%' || UPPER(pName) || '%'));
END;
Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: