Post reply

User Defined Function called from a Stored Procedure only returning a single character.

  • July 21, 2011 at 2:31 pm

    #241865

    I am calling a function to parse out a string like '7108.5W X 30D X 30H' when I debug the program the return value for the W is correct. The value in the function is 7108.5 which is what I want. When it gets back to the select statement in the SP it is only showing the 7 or first digit.

    Any body know why this would happen?

    Thanks

    Below is the code for the function and the select statement in the SP;

    ALTER function [dbo].[udf_parseWidth](@strvalue varchar(500)) returns varchar as

    begin

    Declare @w as varchar(12), @d as varchar(12), @h as varchar(12)

    Declare @iW as int, @iD as int, @iH as int

    set @iW=(select (CHARINDEX('W', @strvalue)))

    --set @w = (select LEFT(@strvalue, @iW-1))

    select @W = LEFT(@strvalue, @iW-1)

    return @w

    end

    Code in SP;

    'Width' =

    Case

    when p.dimension like '%W%' and p.dimension not like '(%' then dbo.udf_parseWidth(p.Dimension)

    end

    Sample to parse the string fields of Width, Depth, Height

    Declare @strvalue as varchar(500), @w as varchar(8), @d as varchar(8), @h as varchar(4)

    Declare @iW as int, @iD as int, @iH as int

    set @strvalue = '7108.5W X 30D X 30H'

    set @iW=(select (CHARINDEX('W', @strvalue)))

    set @w = (select LEFT(@strvalue, @iW-1))

    select @w AS Width

    set @iD = (Select (CHARINDEX('D',@strvalue)))

    set @d = ltrim((select SUBSTRING(@strvalue, @iW+4,@iD-(@iW+4))))

    select @d AS Depth

    set @iH = (Select (CHARINDEX('H',@strvalue)))

    set @h = ltrim((select SUBSTRING(@strvalue, @iD+4,@iH-(@iD+4))))

    select @h AS Height

  • July 21, 2011 at 3:02 pm

    #1356770

    ALTER function [dbo].[udf_parseWidth](@strvalue varchar(500)) returns varchar as

    in this case, a varchar with no size is a varchar(1)

    explcitly change it to returns varchar(500) as or whatever you are expecting.

    you might be expecting it to be a varchar(30) by default, but that is true only when using a CAST or CONVERT

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 22, 2011 at 6:41 am

    #1357012

    That did the trick, thanks

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply