SQLTeam.com | Weblogs | Forums

Unable to pass date variables into a stored procedure

oracle

#1

I am getting blank when passing a date parameter into this stored procedure. I can get back some data if I hard code the date ranges.

can someone please provide some insight on this?

CREATE OR REPLACE PROCEDURE GET_USERS(startdate_in IN DATE, enddate_in IN DATE)
IS
BEGIN
DELETE FROM TEMP_TABLE;
INSERT INTO temp_table
(ID, ROLE, DATE_USED, COUNT_S)
SELECT    EMPLOYEE.CUSTOMERID as USER_ID,   'Customer' as ROLE_,  to_char (EMPLOYEE.STARTEDON, 'MM-YYYY') as REQ_MONTH, count (EMPLOYEE.SUBJECT) as SUBJ_COUNT 
FROM     RCUSER.EMPLOYEE  EMPLOYEE     
WHERE     EMPLOYEE.STATEID NOT IN (4,9,16,31,36) AND (EMPLOYEE.AGENTID=0)  AND (EMPLOYEE.STARTEDON >= to_date(startdate_in,'yyyy-mm-dd')) 
AND (EMPLOYEE.STARTEDON < to_date(enddate_in,'yyyy-mm-dd'))  
Group by CUSTOMERID, to_char (EMPLOYEE.STARTEDON, 'MM-YYYY')
UNION
SELECT    EMPLOYEE.PERFORMERID as USER_ID,   'Performer' as ROLE_,  to_char (EMPLOYEE.STARTEDON, 'MM-YYYY') as REQ_MONTH, count (EMPLOYEE.SUBJECT) as SUBJ_COUNT 
FROM     RCUSER.EMPLOYEE          EMPLOYEE     
WHERE     EMPLOYEE.PEID IN (1,4,6) AND EMPLOYEE.STATEID NOT IN (4,9,16,31,36) AND (EMPLOYEE.AGENTID=0)  AND (EMPLOYEE.STARTEDON >= to_date(startdate_in,'yyyy-mm-dd')) 
AND (EMPLOYEE.STARTEDON < to_date(enddate_in,'yyyy-mm-dd')) 
Group by PERFORMERID, to_char (EMPLOYEE.STARTEDON, 'MM-YYYY')
UNION
SELECT    EMPLOYEE.PERFORMERID as USER_ID,   'Approver' as ROLE_,  to_char (EMPLOYEE.STARTEDON, 'MM-YYYY') as REQ_MONTH, count (EMPLOYEE.SUBJECT) as SUBJ_COUNT 
FROM     RCUSER.EMPLOYEE          EMPLOYEE    
WHERE     EMPLOYEE.PEID IN (2,3) 
AND EMPLOYEE.STATEID NOT IN (4,9,16,31,36) 
AND (EMPLOYEE.AGENTID=0)  
AND (EMPLOYEE.STARTEDON >= to_date(startdate_in,'yyyy-mm-dd')) 
AND (EMPLOYEE.STARTEDON < to_date(enddate_in,'yyyy-mm-dd'))  
Group by PERFORMERID, to_char (EMPLOYEE.STARTEDON, 'MM-YYYY');

END;


#2

Looks like it is Oracle code? This is a Microsoft SQL Server forum. You probably would get better/faster responses if you post to an Oracle forum.


#3

Yep, I'm pretty sure it's Oracle.

Given that, the startdate_in and enddate_in are aleady dates, you don't want to use "TO_DATE" on them, just compare the column directly to those params.


#4

Yes, TO_DATE was causing my issue. All working now:)