Insert a column in dynamic size based on function argument

Question

I'm trying to attach something to char (n), where n is the function argument

ALTER FUNCTION FixMe(@colName varchar, @width integer) RETURNS varchar
AS BEGIN
    RETURN CAST(@colName as char(@width))
END

This code gives an error

Incorrect syntax near '@width'.

I also tried to accomplish this using EXEC()via:

EXEC('set @retval = CAST(@colName as char(' + @width + '))')

But then I started in

Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.

Answer 1

Even if you manage to get this to work in a function, your statement RETURNS varcharwill result in the result being implicitly converted to varchar(1)in the output.

I assume this is related to your previous question, in which case this may work better for you.

ALTER FUNCTION FixMe(@colvalue VARCHAR(8000),
                     @width    INTEGER)
RETURNS VARCHAR(8000)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
  BEGIN
      RETURN REPLACE(RTRIM(@colvalue), ' ', ' ') + 
               CASE
                   WHEN @width > LEN(@colvalue) 
                   THEN REPLICATE(' ', @width - LEN(@colvalue))
                   ELSE ''
               END        
  END