学步园

使用了一个游标，并分解一个字符串：

DECLARE @flag NVARCHAR(50),@i INT
DECLARE @r1 NVARCHAR(50),@r2 NVARCHAR(50),@assay NVARCHAR(50)
SET @flag='20100427181006'
SET @assay=''

--使用游标
DECLARE getAssayValue CURSOR FOR
    SELECT ASSAY FROM dbo.KH_SampleBarcode1  WHERE POSITIONBC NOT IN (SELECT BCNAME FROM dbo.KH_BarCode) AND FLAG=@flag
OPEN getAssayValue
FETCH NEXT FROM getAssayValue INTO @r1
WHILE @@FETCH_STATUS<>-1
BEGIN
    IF @assay=''
        SET @assay=@r1
    ELSE
    BEGIN
        --以下对字符串做的分解，相当于一个Split方法
        SET @i=CHARINDEX(';',@r1)
        IF @i>0
        BEGIN
            WHILE @i>0
            BEGIN
                SET @r2=LEFT(@r1, @i-1)
                IF CHARINDEX(@r2, @assay)<=0
                    SET @assay=@assay+';'+@r2
                SET @r1=SUBSTRING(@r1, @i+1, LEN(@r1)-@i)
                SET @i=CHARINDEX(';',@r1)
            END
        END
        ELSE
            IF CHARINDEX(@r1, @assay)<=0
                SET @assay=@assay+';'+@r1
    END
    FETCH NEXT FROM getAssayValue INTO @r1
END

CLOSE getAssayValue
DEALLOCATE getAssayValue
PRINT @assay