CODEWITHSUNDEEP
sqlsql-server-2012
Basit
Basit

Reputation: 890

Passing a variable length to declare new varchar in SQL Server

I want to declare a new variable with a length defined by a variable, something like this:

DECLARE @trimStringLength INT;
SET @trimStringLength = 48;

DECLARE @trimString VARCHAR(@trimStringLength);

SET @trimStringLength = '';

But I get an error

Incorrect syntax near @trimStringLength. Expecting ID, integer or QUOTED_ID

Upvotes: 3

Views: 8378

Answers (2)

Mit Bhatt
Mit Bhatt

Reputation: 482

In SQL Server for varchar(n)

Storage size is the actual length in bytes of the data entered, not n bytes.

So, if you are not sure of the size, just declare varchar(max)

To remove last character

SET @trimString = SUBSTRING(@have, 0, LEN(@have)-1)

Upvotes: 2

Y.B.
Y.B.

Reputation: 3586

Unfortunately there is no easy way to dynamically specify variable type in MS SQL Server. The best thing that can be done is dynamically create / alter User-Defined Type and use this in the code.

If you just need to truncate the string by few characters, the correct approach would be to manipulate varchar(4000) / varchar(max) variables with string functions:

DECLARE @oldValue varchar(4000) = '123456789';
DECLARE @newValue varchar(4000) = LEFT(@oldValue, LEN(@oldValue)-1);
SELECT @newValue;
-- Result: 12345678

Upvotes: 2

Related Questions