varchar with length from a stored variable?-CodePudding

Is it possible to call a variable for the length of a field (varchar). Typically I always use nvarchar(max) for most things other than when I need a numeric field for math purposes. This is okay until the tables are big and performance takes a big hit.

Is it possible to create a column using varchar(max), then run a length query to create the highest number of characters (this example is just one record, so I'm not truly filtering on a max value. I understand that). Then store that number into a variable and call that variable into creating a new varchar column?

Other uses would be maybe be storing that output/result as a variable for other statements.

Is this possible?

Thanks.

CREATE TABLE Test (
    MyCol nvarchar(max) 
);

INSERT INTO Test (MyCol)
VALUES ('asdfasdfasdfasdfasdfasdfasdfasdfadfsasdfadfs');

SELECT LEN(MyCol) FROM Test

--44 characters. Store this as output as variable

ALTER TABLE TEST ADD NewMyCol varchar(VARIABLE_HERE?) --LEN from above

UPDATE TEST SET NewMyCol = MyCol FROM test

CodePudding user response：

Comments are accurate, it seems quite dangerous to me to add a column based on the current length stored in the table, and I don't really see the benefit.

However, since people will want to do it regardless of how many people pile on saying it's a bad idea:

DECLARE @len int;
SELECT @len = MAX(LEN(MyCol)) FROM dbo.Test;

DECLARE @sql nvarchar(max) = N'
ALTER TABLE TEST ADD NewMyCol varchar($l$);';

SET @sql = REPLACE(@sql, N'$l$', RTRIM(@len));
EXEC sys.sp_executesql @sql;

Working example in this fiddle.