CODEWITHSUNDEEP
sqlsql-servert-sql
finjo
finjo

Reputation: 376

Conversion error despite using CAST?

I have a cursor which fetches reference codes from a table and increments the code by one if it fits certain criteria. The reference is alphanumeric so it is declared as nvarchar.

To keep things simple, assume that @RefNo = 'v1', with the intention of changing this to v2:

DECLARE @versionNo INT
DECLARE @RefNo nvarchar(50)
DECLARE @NewVersionNo INT
DECLARE @NewRefNo nvarchar(50)

set @VersionNo = Right(@RefNo, 1)
set @NewVersionNo = @versionNo + 1
set @NewRefNo = Left(@RefNo, Len(@RefNo - 1)) + cast(@NewVersionNo as nvarchar)

print @NewRefNo

The final line fails with error Conversion failed when converting the nvarchar value 'v1' to data type int. To an extent I get why this is happening - the '+' operator can't handle nvarchar and int values at the same time - but I would have thought the cast to nvarchar on @NewVersionNo would have avoided that.

Also note that I am using 2008R2 so am unable to use the CONCAT function.

Upvotes: 2

Views: 83

Answers (1)

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14689

you have miss place closing bracket, change your code of line as below

set @NewRefNo = Left(@RefNo, Len(@RefNo) - 1) + cast(@NewVersionNo as nvarchar)
                            -----------^

If @RefNo='V1'

Output:

enter image description here

Upvotes: 6

Related Questions