Convert Stored Procedure of Oracle to SQL Server

Question

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)
AS
V_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 FOR
SELECT 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_visacion
FROM TB_HEAD_PPL
INNER 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_NBR
LEFT JOIN TB_FOLIO_PPL_COD_VISACION ON TB_HEAD_PPL.FOLIO_PPL_SII = 
TB_FOLIO_PPL_COD_VISACION.FOLIO_SII
INNER JOIN TB_STORE_INFO ON TB_HEAD_PPL.GLS_STORE = TB_STORE_INFO.STORE_NBR
LEFT JOIN TB_DETAIL_PPL D ON TB_HEAD_PPL.ID_HEAD_PPL = D.ID_HEAD_PPL
LEFT JOIN tb_item i ON D.GLS_ITEM_NBR = i.ITEM_NBR
where 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 null
GROUP 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_VISACION
ORDER BY fecha;
END;

Answer 1

if object_id('"SP_INFORME_FOLIO_COD_VISACION"', 'P') is not null
  drop procedure "SP_INFORME_FOLIO_COD_VISACION";
go

create PROCEDURE "SP_INFORME_FOLIO_COD_VISACION" (
@IN_CD FLOAT,
@IN_LOCAL FLOAT,
@IN_INICIO  VARCHAR(4000),
@IN_FIN   VARCHAR(4000)
)
AS
BEGIN
DECLARE @V_INICIO1 VARCHAR(30);
DECLARE @V_FIN1 VARCHAR(30);
DECLARE @V_INICIO2 DATETIME2(6);
DECLARE @V_FIN2 DATETIME2(6);
DECLARE @V_LOCAL INT;

SET NOCOUNT ON;
 IF @IN_LOCAL=0 BEGIN
  SET @V_LOCAL = null;
 END
 ELSE BEGIN
  SET @V_LOCAL=@IN_LOCAL;
 END 

 IF @IN_INICIO IS NOT NULL BEGIN
  SET @V_INICIO2 = CONVERT(VARCHAR(10), @IN_INICIO, 104) + ' ' + CONVERT(VARCHAR(8), @IN_INICIO, 108);
 END 

 IF @IN_FIN IS NOT NULL BEGIN
  SET @V_FIN2 = CONVERT(VARCHAR(10), @IN_FIN, 104) + ' ' + CONVERT(VARCHAR(8), @IN_FIN, 108);
 END 

SELECT TB_LOG_EJECUCION.FECHORA_FLAG02 AS fecha,
ISNULL(TB_HEAD_PPL.GLS_STORE, '')+ '-'+ ISNULL(TB_STORE_INFO.STORE_NAME, '') AS CD,
TB_HEAD_PPL.FOLIO_PPL_SII   AS guia,
TB_FOLIO_PPL_COD_VISACION.CODIGO_VISACION AS cod_visacion
FROM TB_HEAD_PPL
INNER 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_NBR
LEFT JOIN TB_FOLIO_PPL_COD_VISACION 
    ON TB_HEAD_PPL.FOLIO_PPL_SII = TB_FOLIO_PPL_COD_VISACION.FOLIO_SII
INNER JOIN TB_STORE_INFO 
    ON TB_HEAD_PPL.GLS_STORE = TB_STORE_INFO.STORE_NBR
LEFT JOIN TB_DETAIL_PPL D 
    ON TB_HEAD_PPL.ID_HEAD_PPL = D.ID_HEAD_PPL
LEFT JOIN tb_item i 
    ON D.GLS_ITEM_NBR = i.ITEM_NBR
where TB_LOG_EJECUCION.FECHORA_FLAG02 BETWEEN ISNULL(@V_INICIO2, TB_LOG_EJECUCION.FECHORA_FLAG02) AND ISNULL(@V_FIN2, TB_LOG_EJECUCION.FECHORA_FLAG02)
    AND TB_HEAD_PPL.ID_CD_NBR = @IN_CD AND TB_HEAD_PPL.GLS_STORE = ISNULL(@V_LOCAL, TB_HEAD_PPL.GLS_STORE)
AND i.ITEM_DEPT_NBR = 83 and D.GLS_PRODUCTION_LOT_NUMBER is not null
GROUP BY TB_LOG_EJECUCION.FECHORA_FLAG02, ISNULL(TB_HEAD_PPL.GLS_STORE, '')+ '-'+  ISNULL(TB_STORE_INFO.STORE_NAME, ''),
TB_HEAD_PPL.FOLIO_PPL_SII, TB_FOLIO_PPL_COD_VISACION.CODIGO_VISACION
ORDER BY fecha;
END;

Convert Online

HTH!