finjo finjo - 2 months ago 18
SQL Question

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.

Answer

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