Post reply

declare @a = max(len(fieldX)) > convert(varchar(@a),fieldX)) - why does this fail?

  • August 11, 2011 at 4:31 am

    #255640

    For formatting reasons I want to:

    DECLARE @a INT

    SELECT @a = MAX(LEN(fieldX)) FROM tabY

    SELECT @a -- result is 23

    SELECT CONVERT(VARCHAR(@a), fieldX)) FROM tabY

    -- but the sql fails.

    This works:

    SELECT CONVERT(VARCHAR(23, fieldX)) FROM tab)

    Why can't I use a variable in my convert statement?

    Thanks. Sorry if it's a dumb one - I have a feeling it will be but it's a hard one to google.

  • August 11, 2011 at 4:43 am

    #1367150

    Will it work for you?

    DECLARE @a INT;

    DECLARE @my_sql VARCHAR(MAX);

    SELECT @a = MAX(LEN(Id)) FROM Account;

    SET @my_sql = 'SELECT CONVERT(VARCHAR('+CAST(@a AS VARCHAR(10))+'), Id) FROM Account';

    EXEC (@my_sql);

  • August 11, 2011 at 4:48 am

    #1367154

    You can use dynamic SQL, but it has its pitfalls - search for "curses and blessings of dynamic SQL" for a full discussion. Why not just take the maximum length you're ever going to get, and convert to that? If you're doing this for formatting reasons, why not let the presentation layer size your column for you?

    SELECT CAST(fieldX AS varchar(1000))

    If you don't feel safe picking an arbitrary value, you can use varchar(max). What is the data type of fieldX, by the way?

    John

  • August 11, 2011 at 5:08 am

    #1367166

    Thank you both.

    I would prefer to avoid dynamic sql but it's an option, & probly what I'll go for so thanks.

    I need to run this on multiple servers, so, yes, I could hardcode the max(len) but i just wanted it to be a little cleverer & cope with changes. It's not really important, it just bugs me that I don't understand why it doesn't work. It should be really simple. The field is a varchar.

    It's just a power shell script outputting to a notepad file, nothing with sophisticated formatting options.

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

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