Conditional Clauses in Where or And Section of a SQL statement in an Oracle Stored Procedure
Posted on 2008-10-22
I am converting from MS SQL Server to Oracle. I've been tackling the problems one by one (with the help of Experts-exchange). However this latest one is stumping me.
create or replace spGetTeams(teamID in number, teamName varchar2, crGetTeamInfo out sys_cursor)
open crGetTeamInfo for
Select team_name, team_id, team_record
where isActive = 1
And (team_id = teamID or teamID is null or teamID like '-1)
And (team_name = teamName or teamName is null or teamName like '%'||-1||'%')
The person that made the Stored procedure in MS SQL Server explained to me that that the -1 will return all records. So basically it will try to match to the parameter passed, or null records, or all records. How would I go about doing this in PL/SQL?