How to Increment NVarchar Value in While Loop Using TSQL?

Question

I am trying to increment nvarchar value of a variable like this;

    declare @i int
    set @i = 0
    -- I want a book `@BookSerialNo`  to be increment like e.g abcde-1, abcde-2 
    set @BookSerialNo = CAST(@BookSerialNo +'-' + @i as nvarchar(50));
    WHILE(@i<>@Quantity)
        BEGIN
            INSERT INTO Library.BookDetail
            (
                BookId,
                BookSerialNo,
                CreatedBy,
                CreateDate,
                UpdateDate,
                Updateby
            )
            VALUES
            (
                @BookId,
                @BookSerialNo,
                @CreatedBy,
                @CreatedDate,
                @UpdatedDate,
                @UpdatedBy
            )
            SET @i = @i+1;
        END

So, My question is,
How to Increment the value of @BookSerialNo eache time int the loop?
I want it like e.g 'abcdef-1', 'abcdef-2', 'abcdef-3', I just want to include the numbers after the '-'.

Answer 1

try this..

    set @BookSerialNo = @BookSerialNo +'-0';
    WHILE(@i<>@Quantity)
        BEGIN
            INSERT INTO Library.BookDetail
            (
                BookId,
                BookSerialNo,
                CreatedBy,
                CreateDate,
                UpdateDate,
                Updateby
            )
            VALUES
            (
                @BookId,
                @BookSerialNo,
                @CreatedBy,
                @CreatedDate,
                @UpdatedDate,
                @UpdatedBy
            )
            SET @i = @i+1;
            --increment the serialno also
            SET @BookSerialNo = SUBSTRING(@BookSerialNo, 0, CHARINDEX('-',@BookSerialNo)+1);  
            set @BookSerialNo = @BookSerialNo + CAST (@i as nvarchar(50));
        END

Answer 2

Just move the set inside the while ?

WHILE(@i<>@Quantity)
    BEGIN
    set @BookSerialNo = cast(@BookSerialNo as nvarchar(50)) +'-' + 
                        cast(@i as nvarchar(50));
    ....
    SET @i = @i+1;
    END