AKY King of Life
Published in : 2022-03-02
I have a stored procedure which is compiled and stored in an Oracle database. I want to convert and then run this stored procedure in SQL Server.
Here is the stored procedure in Oracle - how to convert this code to SQL Server to run on this ?
create or replace PROCEDURE "SP_ora" (IN_CD IN NUMBER,IN_LOCAL IN NUMBER,IN_INI IN VARCHAR2,IN_FIN IN VARCHAR2,cv_1 OUT SYS_REFCURSOR)ASV_INI1 VARCHAR2(30);V_FIN1 VARCHAR2(30);V_INI2 TIMESTAMP;V_FIN2 TIMESTAMP;V_LOCAL NUMBER(5);BEGIN IF IN_LOCAL=0 THEN V_LOCAL := null; ELSE V_LOCAL:=IN_LOCAL; END IF; IF IN_INI IS NOT NULL THEN V_INICIO2 := TO_TIMESTAMP(CONCAT(IN_INI, ' 00:00:00'), 'dd/MM/yy HH24:MI:SS'); END IF; IF IN_FIN IS NOT NULL THEN V_FIN2 := TO_TIMESTAMP(CONCAT(IN_FIN, ' 23:59:59'), 'dd/MM/yy HH24:M I:SS'); END IF;OPEN cv_1 FORSELECT TB_LOG_E.FE_FLAG2 AS fecha,TB_HEAD_PPL.GLS_STORE|| '-'|| TB_STORE_INFO.STORE_NAME AS CD,TB_HEAD_PPL.FOLIO_PPL_SII AS guia,TB_FOLIO_PPL_COD_VISACION.CODIGO_VISACION AS cod_visacionFROM TB_HEAD_PPLINNER JOIN TB_LOG_EJECUCION ON TB_HEAD_PPL.ID_HEAD_PPL = TB_LOG_EJECUCION.ID_HEAD_PPL AND TB_HEAD_PPL.ID_CD_NBR = TB_LOG_EJECUCION.ID_CD_NBRLEFT JOIN TB_FOLIO_PPL_COD_VISACION ON TB_HEAD_PPL.FOLIO_PPL_SII = TB_FOLIO_PPL_COD_VISACION.FOLIO_SIIINNER JOIN TB_STORE_INFO ON TB_HEAD_PPL.GLS_STORE = TB_STORE_INFO.STORE_NBRLEFT JOIN TB_DETAIL_PPL D ON TB_HEAD_PPL.ID_HEAD_PPL = D.ID_HEAD_PPLLEFT JOIN tb_item i ON D.GLS_ITEM_NBR = i.ITEM_NBRwhere TB_LOG_EJECUCION.FECHORA_FLAG02 BETWEEN NVL(V_INICIO2, TB_LOG_EJECUCION.FECHORA_FLAG02) AND NVL(V_FIN2, TB_LOG_EJECUCION.FECHORA_FLAG02)AND TB_HEAD_PPL.ID_CD_NBR = IN_CD AND TB_HEAD_PPL.GLS_STORE = NVL(V_LOCAL, TB_HEAD_PPL.GLS_STORE)AND i.ITEM_DEPT_NBR = 83 and D.GLS_PRODUCTION_LOT_NUMBER is not nullGROUP BY TB_LOG_EJECUCION.FECHORA_FLAG02,TB_HEAD_PPL.GLS_STORE|| '-'|| TB_STORE_INFO.STORE_NAME,TB_HEAD_PPL.FOLIO_PPL_SII,TB_FOLIO_PPL_COD_VISACION.CODIGO_VISACIONORDER BY fecha;END;
Join our community and get the chance to solve your code issues & share your opinion with us
Sign up Now
Shilpa Date : 2022-03-02
Best answers
10
Best answers
10
[Possible Duplicate]
You can find whole converted stored procedure according to your question IN THIS THREAD.