Define varchar with variable length

Question

I am attempting to modify the datatype of a specific column in a specific table in a SQL Server 2012 database. in the beginning of the script, the user will be setting the new desired length of the column datatype. However when I attempt to alter the column in the table to set the new varchar length I am getting an error.

Here is the code snippet and the resulting error:

Declare @newPrecis int

Set @newPrecis = 23 -- New length of the index

Alter Table [dbo].[VTAB0047] Alter Column VAL varchar(@newPrecis)

Error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '@newPrecis'.

Currently the length of the VAL column is varchar(20) and I'd like it to be set to a length of 23 or whatever length is inputted in the set statement.

I'm ignoring any type of error checking at the moment because I'm simply trying to get the basic functionality down.

Solution 1

if you want to be able to change the length of a varchar column dynamically then use dynimic sql, because varchar(n) does not allow parameters for n:

declare @sql varchar(500);
Declare @newPrecis int
Set @newPrecis = 23 --or any other value 

set @sql='Alter Table [dbo].[VTAB0047] Alter Column VAL varchar('+cast(@newPrecis as varchar(2))'+')'
exec(@sql)

or you could directly use :

Alter Table [dbo].[VTAB0047] Alter Column VAL varchar(23)

Note: if your new value for n be less than old value then you may get String or binary data would be truncated error if you had value with the length greater than new value for length.

Solution 2

Declare @newPrecis  int    
Declare @query varchar(max)

Set @newPrecis  = 23; -- New length of the index
set @query ='Alter Table [dbo].[VTAB0047] Alter COLUMN  VAL varchar('
  + Cast(@newPrecis  as varchar) +')'

 exec (@query)

Solution 3

If this is an input parameter at the top of a script, the ALTER TABLE statement will have to be done using Dynamic SQL. For example.

DECLARE @SQL VARCHAR(MAX)
DECLARE @newPrecis INT

SET @SQL = 'ALTER TABLE dbo.VTAB0047 ALTER COLUMN VAL VARCHAR(' + CAST(@newPrecis AS VARCHAR(20)) + ')'
EXEC(@SQL)