Post reply

CONVERT(varchar(@MAXLENGTH),x) FROM y

  • November 1, 2011 at 5:15 am

    #390734

    Not sure how to google for this ...

    proc outputs to text file.

    for formatting reasons only I want to do this:

    DECLARE @maxlength TINYINT

    SELECT @maxlength = MAX(LEN(x)) FROM y

    -- SELECT @maxlength -- (output is 23)

    SELECT CONVERT(VARCHAR(@maxlength),x) FROM y

    -- this fails.

    SELECT CONVERT(VARCHAR(23),x) FROM y

    -- this works

    Am I being a spanner?

    Thank you.

  • November 1, 2011 at 5:38 am

    #1401529

    Your final select will have to be dynamic sql.

    You cannot do VARCHAR(@variable).

  • November 1, 2011 at 5:50 am

    #1401540

    Or this may help.

    declare @maxlength TINYINT = 10

    declare @String Varchar(100) = 'abcdefghijklmnopqrstuvwxyz'

    select @String, LEFT(@String, @maxlength)

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 1, 2011 at 5:58 am

    #1401545

    Oh right, I thought the variable would be evaluated first in it's () and applied to the rest of the statement but I guess I've got this wrong.

    Thanks for the dynamic tip.

    I think I'm having one of those days.

  • November 1, 2011 at 6:21 am

    #1401560

    Actually Phil's answer is brilliant.

    Great way to think outside the box! :w00t:

  • November 1, 2011 at 8:25 am

    #1401656

    Perfect, thank you too!

Viewing 6 posts - 1 through 5 (of 5 total)

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