locked
Issues while creating functions RRS feed

  • Question

  • Dear All,

    I am very new to sql server, while am creating function with varchar returns in sql server giving some errors,

    Please go through this, if there is any issue with syntax. Below mentioned is the code.

    CREATE FUNCTION Fn_Check_Customer_Credit (@AcctCode VARCHAR(20), @CurrentAmount NUMERIC(20,2), @CurrentDate DATETIME, @CompCode VARCHAR(20), @CurrentUser VARCHAR(20)) 
    RETURNS VARCHAR(100) 
    AS
    BEGIN
      DECLARE @Str_Block_Limit     VARCHAR(100)
      DECLARE @Str_Check_Days      VARCHAR(100)
      DECLARE @Str_Acct_Lock       VARCHAR(10)
      DECLARE @Str_Return          VARCHAR(100)
      DECLARE @Num_Credit_Limit    NUMERIC(20,8)
      DECLARE @Num_Credit_Days     NUMERIC(20,8)
      DECLARE @Num_Current_Credit  NUMERIC(20,8)
      DECLARE @Num_Total_Pending   NUMERIC(20,8)
      DECLARE @Num_Temp            INT
      DECLARE @Num_CurrentBal      NUMERIC(20,8)
      
      SELECT @Num_Temp = COUNT(*) FROM GN_USER_SETTINGS_DETAIL WHERE USER_NAME = @CurrentUser AND SETUP_CODE = 3;  -- Credit Limit Exception
      SELECT @Str_Block_Limit = CONFIG_VALUE FROM GN_ELIXIR_CONFIG WHERE CONFIG_NAME = 'BLOCK_CUSTOMER_CREDIT_LIMIT' AND COMP_CODE = @CompCode;     
      SELECT @Num_Credit_Limit = CREDIT_LIMIT, @Num_Credit_Days = CREDIT_DAYS, @Str_Acct_Lock = LOCK_ACCT FROM AC_SL_MASTER WHERE ACCT_CODE = @AcctCode;
      SELECT @Num_CurrentBal = (SUM(A.AMOUNT) + SUM(A.DN_AMOUNT))  
                FROM (SELECT SUM(ISNULL(DOC_BALANCE, 0)) AMOUNT, 0 DN_AMOUNT 
                      FROM Fn_Aging(@CurrentDate,@CompCode,@AcctCode)
                      UNION ALL 
                      SELECT SUM(ISNULL(ITEM_QTY, 0) * ISNULL(ITEM_RATE, 0) - ISNULL(DISCOUNT, 0)) AMOUNT, SUM(ISNULL(ITEM_QTY, 0) * ISNULL(ITEM_RATE, 0) - ISNULL(DISCOUNT, 0)) DN_AMOUNT
                      FROM VW_PENDING_DNOTE 
                      WHERE ACCT_CODE = @AcctCode) A;
      IF @Str_Acct_Lock = 'Y'  -- CURRENT CUSTOMER IS LOCKED.
        RETURN 'Account is Locked';
      IF @Str_Block_Limit = 'N'  -- NO NEED TO CHECK THE CREDIT LIMIT.
        RETURN NULL;
          
      IF ISNULL(@Num_Temp, 0) > 0  -- CUSER IS ALLOWED TO ENTER BEYOND THE CREDIT LIMIT OF THE CUSTOMERS.
        RETURN NULL;
      IF (@Num_Credit_Limit < ISNULL(@Num_CurrentBal, 0) + ISNULL(@CurrentAmount, 0)) AND (ISNULL(@Num_Credit_Limit, 0) <> 0)  ---- CURRENT CREDIT IS GREATER THAN OR EQUAL TO THE CREDIT LIMIT.
        RETURN 'Credit Limit Exceeded.';
      ELSE 
        RETURN '0';
    END;


    Thursday, July 14, 2016 7:00 AM

Answers

  • Always the last statement of the function must be a return statement. Use the below code to resolve the issue.

    CREATE FUNCTION Fn_Check_Customer_Credit (@AcctCode VARCHAR(20), @CurrentAmount NUMERIC(20,2), @CurrentDate DATETIME, @CompCode VARCHAR(20), @CurrentUser VARCHAR(20)) 
    RETURNS VARCHAR(100) 
    AS
    BEGIN
      DECLARE @Str_Block_Limit     VARCHAR(100)
      DECLARE @Str_Check_Days      VARCHAR(100)
      DECLARE @Str_Acct_Lock       VARCHAR(10)
      DECLARE @Str_Return          VARCHAR(100)
      DECLARE @Num_Credit_Limit    NUMERIC(20,8)
      DECLARE @Num_Credit_Days     NUMERIC(20,8)
      DECLARE @Num_Current_Credit  NUMERIC(20,8)
      DECLARE @Num_Total_Pending   NUMERIC(20,8)
      DECLARE @Num_Temp            INT
      DECLARE @Num_CurrentBal      NUMERIC(20,8)
      
      DECLARE @ReturnVal varchar(100) 
      
      SELECT @Num_Temp = COUNT(*) FROM GN_USER_SETTINGS_DETAIL WHERE USER_NAME = @CurrentUser AND SETUP_CODE = 3;  -- Credit Limit Exception
      SELECT @Str_Block_Limit = CONFIG_VALUE FROM GN_ELIXIR_CONFIG WHERE CONFIG_NAME = 'BLOCK_CUSTOMER_CREDIT_LIMIT' AND COMP_CODE = @CompCode;     
      SELECT @Num_Credit_Limit = CREDIT_LIMIT, @Num_Credit_Days = CREDIT_DAYS, @Str_Acct_Lock = LOCK_ACCT FROM AC_SL_MASTER WHERE ACCT_CODE = @AcctCode;
      SELECT @Num_CurrentBal = (SUM(A.AMOUNT) + SUM(A.DN_AMOUNT))  
                FROM (SELECT SUM(ISNULL(DOC_BALANCE, 0)) AMOUNT, 0 DN_AMOUNT 
                      FROM Fn_Aging(@CurrentDate,@CompCode,@AcctCode)
                      UNION ALL 
                      SELECT SUM(ISNULL(ITEM_QTY, 0) * ISNULL(ITEM_RATE, 0) - ISNULL(DISCOUNT, 0)) AMOUNT, SUM(ISNULL(ITEM_QTY, 0) * ISNULL(ITEM_RATE, 0) - ISNULL(DISCOUNT, 0)) DN_AMOUNT
                      FROM VW_PENDING_DNOTE 
                      WHERE ACCT_CODE = @AcctCode) A;
      IF @Str_Acct_Lock = 'Y'  -- CURRENT CUSTOMER IS LOCKED.
        SELECT  @ReturnVal = 'Account is Locked';
      IF @Str_Block_Limit = 'N'  -- NO NEED TO CHECK THE CREDIT LIMIT.
        SELECT @ReturnVal = NULL;
          
      IF ISNULL(@Num_Temp, 0) > 0  -- CUSER IS ALLOWED TO ENTER BEYOND THE CREDIT LIMIT OF THE CUSTOMERS.
        SELECT @ReturnVal = NULL;
        
      
        
      IF (@Num_Credit_Limit < ISNULL(@Num_CurrentBal, 0) + ISNULL(@CurrentAmount, 0)) AND (ISNULL(@Num_Credit_Limit, 0) <> 0)  ---- CURRENT CREDIT IS GREATER THAN OR EQUAL TO THE CREDIT LIMIT.
        SELECT @ReturnVal = 'Credit Limit Exceeded.';
      ELSE 
        SELECT @ReturnVal = '0';
        
       RETURN @ReturnVal 
        
    END;



    • Edited by senthilswing Thursday, July 14, 2016 7:13 AM
    • Proposed as answer by senthilswing Thursday, July 14, 2016 7:24 AM
    • Marked as answer by JerinBaby Thursday, July 14, 2016 7:25 AM
    Thursday, July 14, 2016 7:12 AM