Advertisement
mhamdani049

AKI-REFILL-13

Jun 29th, 2020
1,654
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.13 KB | None | 0 0
  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_13_PARTNO_VALIDATE'
  8.     AND ROUTINE_TYPE = N'PROCEDURE'
  9. )
  10. DROP PROCEDURE spex.SP_AMANAH_RE_FILL_13_PARTNO_VALIDATE
  11. GO
  12.  
  13. CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_13_PARTNO_VALIDATE
  14. (
  15.     @PART_NO_CURRENT VARCHAR(MAX),
  16.     @PART_NO_SCAN VARCHAR(30),
  17.     @USERNAME VARCHAR(20)
  18. )
  19. AS
  20. BEGIN
  21.  
  22.     DECLARE @MSG_TEXT VARCHAR(MAX) = ''
  23.     DECLARE @RES_TEXT VARCHAR(MAX) = ''
  24.  
  25.     SET NOCOUNT ON;
  26.     CREATE TABLE #TB_T_DATA (
  27.         RESULT VARCHAR(MAX),
  28.         Message VARCHAR(MAX)
  29.     )
  30.  
  31.     EXEC spex.usp_CutPartNo @PART_NO_SCAN OUTPUT
  32.     IF (@PART_NO_CURRENT = @PART_NO_SCAN)
  33.     BEGIN
  34.         SET @MSG_TEXT = @PART_NO_SCAN;
  35.         SET @RES_TEXT = 'Success'
  36.     END
  37.     ELSE
  38.     BEGIN
  39.         SET @MSG_TEXT = 'INVALID PART NUMBER!';
  40.         SET @RES_TEXT = 'Failed'
  41.     END
  42.  
  43.     INSERT INTO #TB_T_DATA
  44.     VALUES (@RES_TEXT, @MSG_TEXT)
  45.  
  46.     SELECT RESULT, Message
  47.     FROM #TB_T_DATA
  48.  
  49. END
  50. GO
  51.  
  52. EXECUTE spex.SP_AMANAH_RE_FILL_13_PARTNO_VALIDATE '111150Y030', '111150Y030 X', 'AMANAH.Yusup';
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement