CODEWITHSUNDEEP
sqlsql-server-2008
Jama A.
Jama A.

Reputation: 16099

Changing ntext column type to varchar(n) in SQL Server

I'm having a problem when converting all table columns with ntext type in my database. I wrote this query in order to alter my columns, but there is a syntax error:

ALTER TABLE mytable ALTER COLUMN mycolumn 
VARCHAR(SELECT MAX(DATALENGTH(mycolumn)) FROM  mytable);

Even though SELECT MAX(DATALENGTH(mycolumn)) FROM mytable is returning the correct number, the query cannot be executed.

The syntax error is:

Incorrect syntax near the keyword 'select'.(which is inside the varchar)

How can I solve the problem?

Upvotes: 3

Views: 4053

Answers (3)

James Curtis
James Curtis

Reputation: 794

You will need to execute this as dynamic sql because the size of the column cannot be a variable.

DECLARE @Length int = SELECT MAX(DATALENGTH(mycolumn)) FROM mytable
DECLARE @MyTable varchar(100) = 'mytable'
DECLARE @MyColumn varchar(100) = 'mycolumn'
DECLARE @SQL varchar(8000) = 'ALTER TABLE ' + @MyTable +' ALTER COLUMN '+ @MyColumn +' VARCHAR(' + CONVERT(varchar, @Length) + ')'
EXEC(@SQL)

The benefit of this is you could loop over sys.objects and sys.columns to find all ntext columns and convert them to varchar.

Upvotes: 5

Koerr
Koerr

Reputation: 15733

Trying:

SET @maxLength = SELECT MAX(DATALENGTH(mycolumn)) FROM mytable;

ALTER TABLE mytable ALTER COLUMN mycolumn VARCHAR(@maxLength);

Upvotes: 0

Leniel Maccaferri
Leniel Maccaferri

Reputation: 102448

I fear you cannot do this inside an ALTER COLUMN statement. At least I've never seen this.

You should do this way:

Calculate the max data length in that column (you're already doing this):

SELECT MAX(DATALENGTH(mycolumn)) FROM mytable

Now just replace the value you got in the previous query:

ALTER TABLE mytable ALTER COLUMN mycolumn VARCHAR(MAXVALUEFROMPREVIOUSQUERY);

Upvotes: 0

Related Questions