title: Inconsistent behavior with (relatively simple) UDF returning a string result
body: <p>I'm writing a function that UNINTENTIONALLY seems to work differently depending on how it is invoked.
  The function should do the following:
  ACCEPT 2 parameters, a string and an integer
  Return 1 string of variable length that might be any length from 1 to several thousand characters.</p>
  
  <p>If the string is null, the function returns a string of spaces equal to the integer param.
  If the string is shorter than the length, it will pad the string to the desired length.
  If the string is longer than the length, it will return a cropped string.
  If the string is the correct length, it will return the string.</p>
  
  <p>The input string and the output string should work for any string variable, regardless of length.  </p>
  
  <pre><code>USE [mydb]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER FUNCTION [dbo].[fix_len]
  (
      @v varchar(max),
      @len_v int
  )
  RETURNS varchar(max)
  AS
  BEGIN
      -- Declare the return variable here
      DECLARE @cur_len as int
      DECLARE @ret_val as varchar(max)
  
      set @ret_val = ISNULL(@v, space(@len_v))
      set @cur_len = dataLENgth(@ret_val)
  
      set @ret_val = case when @cur_len &lt; @len_v then @ret_val + SPACE(@len_v-@cur_len)
                          when @cur_len &gt; @len_v then SUBSTRING(@ret_val,1, @len_v)
                          else @ret_val
                     end
  
      RETURN @ret_val
  
  END
  </code></pre>
  
  <p>Now here's the problem.  IF I invoke this function like this:</p>
  
  <p>SELECT dbo.fix_len(a, 10) + dbo.fix_len(b, 10) + dbo.fix_len(c, 10) + dbo.fix_len(d, 10) from sometable </p>
  
  <p>THEN it (the fix_len function) works JUST FINE!</p>
  
  <p>However, if I invoke it like this:
  SELECT dbo.fix_len(a, 10), dbo.fix_len(b, 10), dbo.fix_len(c, 10), dbo.fix_len(d, 10) from sometable </p>
  
  <p>THEN it (the fix_len function or maybe sql server) wants to pad each field out to max chars.</p>
  
  <p>The current purpose of this for me at the current time is to create a text file that concatenates the fields together.  I invoke a stored procedure through the bcp command (in the cmd shell).  It works fine, IF I MANUALLY CONCATENATE THE STRINGS MYSELF as in the first select.</p>
  
  <p>However, if I let BCP figure it out, it pads each field out.  But I don't think BCP is the problem.  If I just execute each of the two selects from a query window, the first select is right - the second adds the padding.</p>
  
  <p>Any ideas what I'm doing wrong - and just as importantly, what I can do to fix it?</p>