如何使用TSQL在While循环中增加NVarchar值？

Question

我试图增加这样一个变量的nvarchar值；

    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

所以我的问题是

如何在循环中增加@BookSerialNo eache时间的值？

我要它像'abcdef-1'，'abcdef-2'，'abcdef-3'，我只想包括'-‘之后的数字。

Answer 1

试试这个..。

    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

把set移到while里面？

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

Answer 3

假设@BookSerialNo数据类型是nvarchar

Try this

WHILE(@i<>@Quantity)
BEGIN
    set @BookSerialNo = @BookSerialNo +'-' + CONVERT(NVARCHAR(50),@i);
    ....
    SET @i = @i+1;
END