user image

AKY King of Life
Published in : 2022-03-02

Convert Stored Procedure of Oracle to SQL Server

General

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;

Comments

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.

 

Leave a comment

Join us

Join our community and get the chance to solve your code issues & share your opinion with us

Sign up Now

Related posts

Difference between frontend and backend user authentication
Publish date: 2022-02-12 | Comments: 2

Tag: General

[solved] Unable to pull updates from git "sudo git pull"
Publish date: 2021-12-20 | Comments: 1

Tag: General

Connecting jupyter notebook with Android studio
Publish date: 2022-03-05 | Comments: 1

Tag: General

Keycloak db query in source codes [closed]
Publish date: 2022-03-05 | Comments: 0

Tag: General