CODEWITHSUNDEEP
sql-serversql-server-2005t-sql
Jim Mitchener
Jim Mitchener

Reputation: 9003

Cast column to dynamic size based on function argument

I am trying to cast something to char(n) where n is a function argument

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

This code is giving an error of

Incorrect syntax near '@width'.

I have also tried executing this with EXEC() via:

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

But I then run in to

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

Upvotes: 3

Views: 1229

Answers (1)

Martin Smith
Martin Smith

Reputation: 453112

Even if you did manage to get this to work in a function your RETURNS varchar statement would cause the result to be implicitly converted to varchar(1) on the way out.

I assume this is related to your previous question in which case this might 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

Upvotes: 5

Related Questions