Search code examples
sql-serveralternvarchar

If length of nvarchar is x then alter it

I have been making changes to a database and am creating a script so that it can be run on the original database to inherit all the changes I have made.

I need to alter the length of an NVARCHAR, but want to check its length before I alter the column. I am struggling with the syntax a bit, could anyone help me with this?

SELECT LEN(colName) AS MyLength FROM tblName
IF MyLength = 60
BEGIN
    ALTER TABLE tblName
    ALTER COLUMN colName nvarchar(140) NOT NULL
END
GO

If I attempt to run this query in SQL Server Management Studio I get an error message that says:

Invalid column name 'MyLength'.

Solution

  • try this: 

    IF (select max(LEN(colName)) from tblName) = 60
BEGIN
    ALTER TABLE tblName
    ALTER COLUMN colName nvarchar(140) NOT NULL
END
GO