locked
USER DEFINED FUNCTION RETURNING FIRST CHARACTER ONLY RRS feed

  • Question

  • When I execute the function using a SELECT dbo.GETPREVPERIOD statement it only returns the first letter of the value.

    The view only contains one record and the PREV_FIN_PER column are defined as varchar 7

    CREATE FUNCTION GETPREVperiod()  
    RETURNS VARCHAR
    AS
    BEGIN
    DECLARE @PREVPERIOD VARCHAR(15)  

    SELECT top 1  @PREVPERIOD = PREV_FIN_PER   
     from  [dbo].[CURR_FIN_YEAR_VIEW]

    if (@prevperiod is  null)
    BEGIN
    RETURN @PREVPERIOD
    END
    --- Use default workgroup if no match is found
    RETURN 'Default'
    END

      Please help!!!
    Wednesday, August 13, 2014 9:36 AM

Answers

  • Specify the size for the return datatype:

    CREATE FUNCTION GETPREVperiod()  
    RETURNS VARCHAR(100)
    AS
    BEGIN
    DECLARE @PREVPERIOD VARCHAR(15)  

    SELECT top 1  @PREVPERIOD = PREV_FIN_PER   
     from  [dbo].[CURR_FIN_YEAR_VIEW]

    if (@prevperiod is  null)
    BEGIN
    RETURN @PREVPERIOD
    END
    --- Use default workgroup if no match is found
    RETURN 'Default'
    END

    I think you can use 7 size as you metioned the size of table column datatype.
    • Edited by SQLZealots Wednesday, August 13, 2014 10:12 AM
    • Proposed as answer by Russ Loski Wednesday, August 13, 2014 10:17 AM
    • Marked as answer by JUSTANSIE Wednesday, August 13, 2014 10:27 AM
    Wednesday, August 13, 2014 10:11 AM
  • When I execute the function using a SELECT dbo.GETPREVPERIOD statement it only returns the first letter of the value.

    The view only contains one record and the PREV_FIN_PER column are defined as varchar 7

    CREATE FUNCTION GETPREVperiod()  
    RETURNS VARCHAR
    AS
    BEGIN
    DECLARE @PREVPERIOD VARCHAR(15)  

    SELECT top 1  @PREVPERIOD = PREV_FIN_PER   
     from  [dbo].[CURR_FIN_YEAR_VIEW]

    if (@prevperiod is  null)
    BEGIN
    RETURN @PREVPERIOD
    END
    --- Use default workgroup if no match is found
    RETURN 'Default'
    END

      Please help!!!

    You need to specify a length:

    RETURNS VARCHAR(10)

    • Marked as answer by JUSTANSIE Wednesday, August 13, 2014 10:27 AM
    Wednesday, August 13, 2014 10:12 AM

All replies

  • Specify the size for the return datatype:

    CREATE FUNCTION GETPREVperiod()  
    RETURNS VARCHAR(100)
    AS
    BEGIN
    DECLARE @PREVPERIOD VARCHAR(15)  

    SELECT top 1  @PREVPERIOD = PREV_FIN_PER   
     from  [dbo].[CURR_FIN_YEAR_VIEW]

    if (@prevperiod is  null)
    BEGIN
    RETURN @PREVPERIOD
    END
    --- Use default workgroup if no match is found
    RETURN 'Default'
    END

    I think you can use 7 size as you metioned the size of table column datatype.
    • Edited by SQLZealots Wednesday, August 13, 2014 10:12 AM
    • Proposed as answer by Russ Loski Wednesday, August 13, 2014 10:17 AM
    • Marked as answer by JUSTANSIE Wednesday, August 13, 2014 10:27 AM
    Wednesday, August 13, 2014 10:11 AM
  • When I execute the function using a SELECT dbo.GETPREVPERIOD statement it only returns the first letter of the value.

    The view only contains one record and the PREV_FIN_PER column are defined as varchar 7

    CREATE FUNCTION GETPREVperiod()  
    RETURNS VARCHAR
    AS
    BEGIN
    DECLARE @PREVPERIOD VARCHAR(15)  

    SELECT top 1  @PREVPERIOD = PREV_FIN_PER   
     from  [dbo].[CURR_FIN_YEAR_VIEW]

    if (@prevperiod is  null)
    BEGIN
    RETURN @PREVPERIOD
    END
    --- Use default workgroup if no match is found
    RETURN 'Default'
    END

      Please help!!!

    You need to specify a length:

    RETURNS VARCHAR(10)

    • Marked as answer by JUSTANSIE Wednesday, August 13, 2014 10:27 AM
    Wednesday, August 13, 2014 10:12 AM