Using a variable in VARCHAR

[Terri Jenkins]

Has anyone successfully used a variable in the length parameter of VARCHAR? I need to append a comment to a long field that often already has a string in it. I'm checking the length of the field, then I want to append my comment to the end. Below is what I'd like to do, but it only works if I use a literal in place of @LEN in the SET COMMENTS statement:

DECLARE @ADD1 AS VARCHAR(15)
DECLARE @LEN AS INT
SET @ADD1 = 'This is a test.'
SELECT @LEN = LEN(CONVERT(VAR CHAR(1000),COMM ENTS)) FROM PS_EX_SHEET_HDR WHERE SHEET_ID = '0000000080'
PRINT @LEN
UPDATE PS_EX_SHEET_HDR
SET COMMENTS = CONVERT(VARCHAR (@LEN),COMMENTS ) + @ADD1 + @ADD2
WHERE SHEET_ID = '0000000080'

[gpl]

VarChar is a variable length type, it takes up just enough room to hold the contents (up to the limit in its declaration.

If you were using the Char type, then the length is important.

In your case just declare it as VarChar(4096) - and no, you cannot have variable variable-length declaration