I want to convert the following SP to SQL, but I am not able to do ::-
ORACLE SP
create or replace
PROCEDURE "USP_TEST"
(
p_CURPRODUCTTYPE out sys_refcursor,
p_PROD_ID IN TBL_PROD_MASTER.PROD_ID%TYPE :=NULL
) is
begin
open p_CURPRODUCTTYPE for
SELECT PROD_ID,
PROD_CODE,
PROD_NAME,
PRIMARY_UOM,
SECONDARY_UOM
FROM TBL_PROD_MASTER
WHERE TBL_PROD_MASTER.PROD_ID =NVL(p_PROD_ID,TBL_PROD_MASTER.PROD_ID)
ORDER BY TBL_PROD_MASTER.PROD_NAME;
end;
SQL SP : -
CREATE PROCEDURE "USP_TEST"(p_recordset OUT SYS_REFCURSOR ,
p_PROD_ID IN TBL_PROD_MASTER.PROD_ID%TYPE :=NULL
) AS
BEGIN
OPEN p_recordset FOR
SELECT PROD_ID,
PROD_CODE,
PROD_NAME,
PRIMARY_UOM,
SECONDARY_UOM
FROM TBL_PROD_MASTER
WHERE TBL_PROD_MASTER.PROD_ID =NVL(p_PROD_ID,TBL_PROD_MASTER.PROD_ID)
ORDER BY TBL_PROD_MASTER.PROD_NAME;
END ;
/
But I am getting the following ERRORs : -
Msg 102, Level 15, State 1, Procedure USP_TEST, Line 1
Incorrect syntax near 'p_recordset'.
Msg 156, Level 15, State 1, Procedure USP_TEST, Line 5
Incorrect syntax near the keyword 'FOR'.
Msg 195, Level 15, State 10, Procedure USP_TEST, Line 12
'NVL' is not a recognized built-in function name.