SHARE
TWEET

AKI-REFILL-6

mhamdani049 Jun 29th, 2020 939 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE SPEX_DB;
  2.  
  3. IF EXISTS (
  4. SELECT *
  5.     FROM INFORMATION_SCHEMA.ROUTINES
  6. WHERE SPECIFIC_SCHEMA = N'spex'
  7.     AND SPECIFIC_NAME = N'SP_AMANAH_RE_FILL_5_SCAN_M_RESERVE_LOCATION'
  8.     AND ROUTINE_TYPE = N'PROCEDURE'
  9. )
  10. DROP PROCEDURE spex.SP_AMANAH_RE_FILL_5_SCAN_M_RESERVE_LOCATION
  11. GO
  12.  
  13. CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_5_SCAN_M_RESERVE_LOCATION
  14. (
  15.     @PART_NO NVARCHAR(MAX),
  16.     @RESERVE_LOC NVARCHAR(MAX)
  17. )
  18. AS
  19. BEGIN
  20.  
  21.     DECLARE @MSG_TEXT VARCHAR(MAX) = ''
  22.         ,@PARAM1 VARCHAR(MAX) = ''
  23.         ,@DATEPARAM VARCHAR(MAX)
  24.  
  25.     SET @DATEPARAM = CONVERT(VARCHAR, GETDATE(), 112)
  26.  
  27.     SET NOCOUNT ON;
  28.     CREATE TABLE #TB_T_DATA (
  29.         RESULT VARCHAR(MAX),
  30.         Message VARCHAR(MAX)
  31.     )
  32.  
  33.     BEGIN TRY
  34.         BEGIN TRANSACTION
  35.  
  36.             IF EXISTS (
  37.                 SELECT TOP 1 a.[PART_NO]
  38.                         ,a.[RACK_CD]
  39.                     FROM [SPEX_DB].[spex].[TB_M_RESERVE_LOCATION] a
  40.                     LEFT JOIN [SPEX_DB].[spex].[TB_R_BINNING] b ON a.RACK_CD = b.[RACK_ADDRESS]
  41.                     WHERE a.[RACK_CD] = @RESERVE_LOC AND a.[PART_NO] = @PART_NO
  42.             )
  43.                 BEGIN
  44.  
  45.                     INSERT INTO #TB_T_DATA
  46.                     VALUES ('Success', @MSG_TEXT)
  47.  
  48.                 END
  49.             ELSE
  50.                 BEGIN
  51.                     SET @MSG_TEXT = ERROR_MESSAGE()
  52.  
  53.                     INSERT INTO #TB_T_DATA
  54.                     VALUES ('Failed' ,@MSG_TEXT)
  55.                 END
  56.  
  57.         COMMIT TRANSACTION
  58.     END TRY
  59.     BEGIN CATCH
  60.         ROLLBACK TRANSACTION
  61.  
  62.         SET @MSG_TEXT = ERROR_MESSAGE()
  63.  
  64.         INSERT INTO #TB_T_DATA
  65.         VALUES (
  66.             'Failed'
  67.             ,@MSG_TEXT
  68.             )
  69.     END CATCH
  70.  
  71.     SELECT RESULT
  72.         ,Message
  73.     FROM #TB_T_DATA
  74.  
  75. END
  76. GO
  77.  
  78. EXECUTE spex.SP_AMANAH_RE_FILL_5_SCAN_M_RESERVE_LOCATION '851100D120', 'RA-002';
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top