CODEWITHSUNDEEP
sqloracle-databaseplsql
user1181942
user1181942

Reputation: 1607

Where clause not working in Oracle

My Stored procedure is like:

CREATE OR REPLACE PROCEDURE     Proc_SearchRequests
(
RequestedBy LONG,
FromDate DATE DEFAULT NULL,
ToDate DATE DEFAULT NULL,
RequestedByDesignation VARCHAR2 DEFAULT NULL,
TypeId INT,
CurrentStatusId INT DEFAULT NULL, 
AmountFrom LONG DEFAULT NULL,
AmountTo LONG DEFAULT NULL,
cur_ExcelOutput OUT SYS_REFCURSOR
  )
AS   
BEGIN
OPEN cur_ExcelOutput FOR
SELECT cd.*,
emp.FIRSTNAME || ' ' || emp.LASTNAME   AS ADDEDBYNAME
FROM 
DETAILS cd
LEFT JOIN EMPLOYEES emp ON cd.ADDEDBY = emp.EMPLOYEEID
WHERE
cd.TYPEID=TypeId;   

END;

I am passing executing procedure as:

BEGIN
PROC_SEARCHREQUESTS (110, to_date ('2001-01-01', 'YYYY-MM-DD'), to_date ('9999-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'), null, 2, 0, 0, 0, :cur_exceloutput$REFCURSOR);
END;

But whatever I pass as TypeId I get complete data and not the filtered one.. If i write "cd.TYPEID=2" I get proper result but passing value in varible is not helping.

What can be wrong?.. Am I missing any casting or something else?

Upvotes: 3

Views: 4223

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

When you're writing a stored procedure, you always want to have some sort of naming convention to differentiate local variables, procedure parameters, and database columns. Since identifiers are first resolved using the name of a column in the table and then using the name of a local variable or parameter, both sides of the WHERE clause

WHERE cd.TYPEID=TypeId;

resolves to the TypeID column of the Details table. This query won't ever look at the value in the parameter TypeID.

If you adopt a standard naming convention (i.e. prefixing parameters with p_ and local variables with l_), you won't have to worry about introducing scope resolution bugs.

Something like this should work (though the data types you're using are pretty unusual-- LONG, for example, has been deprecated for a long time so you should really be using a VARCHAR2 there (or a CLOB if you really need more than 32k which seems unlikely given the names)). 

CREATE OR REPLACE PROCEDURE     Proc_SearchRequests
(
  p_RequestedBy LONG,
  p_FromDate DATE DEFAULT NULL,
  p_ToDate DATE DEFAULT NULL,
  p_RequestedByDesignation VARCHAR2 DEFAULT NULL,
  p_TypeId INT,
  p_CurrentStatusId INT DEFAULT NULL, 
  p_AmountFrom LONG DEFAULT NULL,
  p_AmountTo LONG DEFAULT NULL,
  p_cur_ExcelOutput OUT SYS_REFCURSOR
)
AS   
BEGIN
  OPEN p_cur_ExcelOutput FOR
    SELECT cd.*,
           emp.FIRSTNAME || ' ' || emp.LASTNAME   AS ADDEDBYNAME
    FROM 
      DETAILS cd
      LEFT JOIN EMPLOYEES emp ON cd.ADDEDBY = emp.EMPLOYEEID
    WHERE
      cd.TYPEID=p_TypeId;   
END;

Upvotes: 4

Florin Ghita
Florin Ghita

Reputation: 17643

You have name collision. The variable has the same name as column. Change the variable(parameter)

TypeId INT,

into 

p_TypeId INT,

Upvotes: 8

Related Questions