返回从存储过程中在游标中创建的行

Question

我有一个包含三个列的表，每个列都包含逗号分隔的数据，如

AAA,BBB,CCC,DDD,...。

我希望返回一个CROSS JOIN，其中包含来自所有列的所有标记的所有可能组合，以及每一行上的一些其他列。

我有一个拆分函数，它返回表中的标记。我传入每一列，并得到一堆行回来。

我想出的最好的方法就是用游标，一次只取一行。完成交叉连接后，我将所有计算出的行写入work/temp表。一旦处理完所有行，我将从work/temp表中选择返回计算的行。

我的问题是:在没有工作/临时表的情况下，有没有办法做到这一点？

我现在的代码是：

DECLARE cPKG CURSOR FAST_FORWARD FOR SELECT ID, SEARCH, COUNTY, COMPANY FROM DEV..EXPPKG WITH(NOLOCK)
OPEN cPKG
FETCH NEXT FROM cPKG INTO @ID, @SEARCH, @COUNTY, @COMPANY
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO DEV..PKG_DUMP_WORK
                (ID,
                    PKG_CODE,
                    PRICE,
                    CNTY,
                    CPNY,
                    SRCH,
                    SEARCH,
                    COUNTY,
                    COMPANY,
                    SRCH_COUNT,
                    UPDT_DT,
                    UPDT_BY,
                    UPDT_CMT)
    SELECT PKG.ID,
            PKG.PKG_CODE,
            PKG.PRICE,
            CNTY.VALUE AS CNTY,
            CPNY.VALUE AS CPNY,
            SRCH.VALUE AS SRCH,
            PKG.SEARCH,
            PKG.COUNTY,
            PKG.COMPANY,
            PKG.SRCH_COUNT,
            PKG.UPDT_DT,
            PKG.UPDT_BY,
            PKG.UPDT_CMT
    FROM   (SELECT *
            FROM   DEV..EXPPKG WITH(NOLOCK)
            WHERE  ID = @ID) PKG
            CROSS JOIN DBO.Split(@SEARCH, ',') SRCH -- AAA,BBB,CCC...
            CROSS JOIN DBO.Split(@COUNTY, ',') CNTY -- DDD,EEE,FFF..
            CROSS JOIN DBO.Split(@COMPANY, ',') CPNY -- GGG,HHH,KKK...

    FETCH NEXT FROM cPKG INTO @ID, @SEARCH, @COUNTY, @COMPANY
END
CLOSE cPKG
DEALLOCATE cPKG

一些数据：

    INSERT INTO [EXPPKG] ( PKG_CODE, PRICE, SEARCH, COUNTY, COMPANY, SRCH_COUNT, UPDT_DT, UPDT_BY, UPDT_CMT ) VALUES ( 'A-2', 999, 'CO,ER,FC,HB,ST,TX', 'BX,KG,QN,RI', ',AAN,ALR,CITI,GRANITE,HARB,LLS,LTTA,MADI,NARROW,REGENCY,', 6, NULL, NULL, NULL );
    INSERT INTO [EXPPKG] ( PKG_CODE, PRICE, SEARCH, COUNTY, COMPANY, SRCH_COUNT, UPDT_DT, UPDT_BY, UPDT_CMT ) VALUES ( 'AM-2', 999, 'CO,ER,FC,HB,ST,TX', 'MA', ',ALR,CITI,GRANITE,INTER,LTTA,MADI,SKYLINE,', 6, NULL, NULL, NULL );
    INSERT INTO [EXPPKG] ( PKG_CODE, PRICE, SEARCH, COUNTY, COMPANY, SRCH_COUNT, UPDT_DT, UPDT_BY, UPDT_CMT ) VALUES ( 'B-2', 999, 'AR,CO,ER,FC,HB,HI,HL,ST,TX', 'BX,KG,QN,RI', ',C&C,LTTA,', 9, NULL, NULL, NULL );
    INSERT INTO [EXPPKG] ( PKG_CODE, PRICE, SEARCH, COUNTY, COMPANY, SRCH_COUNT, UPDT_DT, UPDT_BY, UPDT_CMT ) VALUES ( 'CA-2', 999, 'CO,ER,FC,HB,HI,ST,TX', 'BX,KG,MA,QN,RI', ',CANY,CHATHAM,TRAK,', 7, NULL, NULL, NULL );
    INSERT INTO [EXPPKG] ( PKG_CODE, PRICE, SEARCH, COUNTY, COMPANY, SRCH_COUNT, UPDT_DT, UPDT_BY, UPDT_CMT ) VALUES ( 'CT-4', 999, 'CO,ER,FC,HB', 'BX,KG,MA,QN,RI', ',CLTLTNY,CTALB,CTIM,CTIM-711,CTIM-CC,CTIM-Q,CTIM-R,CTIMR-O,FNT,FNT-A,FNT-AG,FNT-N,FNT-R,NYLS,TICOR,TICORROC,FNT-RAM,', 4, NULL, NULL, NULL );

Answer 1

可以将整个游标替换为基于集的插入。我也要提醒你不要使用NOLOCK的暗示。它可以并将返回丢失和/或重复的行。还有其他一些讨厌的事情。http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/

INSERT INTO DEV..PKG_DUMP_WORK
(
    ID,
    PKG_CODE,
    PRICE,
    CNTY,
    CPNY,
    SRCH,
    SEARCH,
    COUNTY,
    COMPANY,
    SRCH_COUNT,
    UPDT_DT,
    UPDT_BY,
    UPDT_CMT
)
SELECT PKG.ID,
    PKG.PKG_CODE,
    PKG.PRICE,
    CNTY.VALUE AS CNTY,
    CPNY.VALUE AS CPNY,
    SRCH.VALUE AS SRCH,
    PKG.SEARCH,
    PKG.COUNTY,
    PKG.COMPANY,
    PKG.SRCH_COUNT,
    PKG.UPDT_DT,
    PKG.UPDT_BY,
    PKG.UPDT_CMT
FROM   DEV..EXPPKG PKG WITH(NOLOCK)
CROSS APPLY DBO.Split(PKG.SEARCH, ',') SRCH -- AAA,BBB,CCC...
CROSS APPLY DBO.Split(PKG.COUNTY, ',') CNTY -- DDD,EEE,FFF..
CROSS APPLY DBO.Split(PKG.COMPANY, ',') CPNY -- GGG,HHH,KKK...

Answer 2

尝试使用交叉应用

SELECT *
FROM   DEV..EXPPKG WITH(NOLOCK)
CROSS APPLY DBO.Split(SEARCH, ',') SRCH -- AAA,BBB,CCC...
CROSS APPLY DBO.Split(COUNTY, ',') CNTY -- DDD,EEE,FFF..
CROSS APPLY DBO.Split(COMPANY, ',') CPNY

这是使用泛型拆分函数的解决方案。

DROP FUNCTION SplitString
GO
CREATE FUNCTION SplitString
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Value NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT
      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Value)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

    declare @EXPPKG table ( ID int identity,  PKG_CODE varchar(5) , PRICE int , SEARCH varchar(255), COUNTY varchar(255), COMPANY varchar(255), SRCH_COUNT varchar(255), UPDT_DT varchar(255), UPDT_BY varchar(255), UPDT_CMT varchar(255))
    INSERT INTO @EXPPKG  ( PKG_CODE, PRICE, SEARCH, COUNTY, COMPANY, SRCH_COUNT, UPDT_DT, UPDT_BY, UPDT_CMT ) VALUES ( 'A-2', 999, 'CO,ER,FC,HB,ST,TX', 'BX,KG,QN,RI', ',AAN,ALR,CITI,GRANITE,HARB,LLS,LTTA,MADI,NARROW,REGENCY,', 6, NULL, NULL, NULL );
    INSERT INTO @EXPPKG  ( PKG_CODE, PRICE, SEARCH, COUNTY, COMPANY, SRCH_COUNT, UPDT_DT, UPDT_BY, UPDT_CMT ) VALUES ( 'AM-2', 999, 'CO,ER,FC,HB,ST,TX', 'MA', ',ALR,CITI,GRANITE,INTER,LTTA,MADI,SKYLINE,', 6, NULL, NULL, NULL );
    INSERT INTO @EXPPKG  ( PKG_CODE, PRICE, SEARCH, COUNTY, COMPANY, SRCH_COUNT, UPDT_DT, UPDT_BY, UPDT_CMT ) VALUES ( 'B-2', 999, 'AR,CO,ER,FC,HB,HI,HL,ST,TX', 'BX,KG,QN,RI', ',C&C,LTTA,', 9, NULL, NULL, NULL );
    INSERT INTO @EXPPKG  ( PKG_CODE, PRICE, SEARCH, COUNTY, COMPANY, SRCH_COUNT, UPDT_DT, UPDT_BY, UPDT_CMT ) VALUES ( 'CA-2', 999, 'CO,ER,FC,HB,HI,ST,TX', 'BX,KG,MA,QN,RI', ',CANY,CHATHAM,TRAK,', 7, NULL, NULL, NULL );
    INSERT INTO @EXPPKG  ( PKG_CODE, PRICE, SEARCH, COUNTY, COMPANY, SRCH_COUNT, UPDT_DT, UPDT_BY, UPDT_CMT ) VALUES ( 'CT-4', 999, 'CO,ER,FC,HB', 'BX,KG,MA,QN,RI', ',CLTLTNY,CTALB,CTIM,CTIM-711,CTIM-CC,CTIM-Q,CTIM-R,CTIMR-O,FNT,FNT-A,FNT-AG,FNT-N,FNT-R,NYLS,TICOR,TICORROC,FNT-RAM,', 4, NULL, NULL, NULL );

选择

    select 
            PKG.ID,
            PKG.PKG_CODE,
            PKG.PRICE,
            SRCH.Value AS CNTY,
            CPNY.Value CPNY,
            SRCH.Value AS SRCH,
            PKG.SEARCH,
            PKG.COUNTY,
            PKG.COMPANY,
            PKG.SRCH_COUNT,
            PKG.UPDT_DT,
            PKG.UPDT_BY,
            PKG.UPDT_CMT
             from @EXPPKG PKG
             CROSS APPLY DBO.SplitString(SEARCH, ',') SRCH 
             CROSS APPLY DBO.SplitString(COUNTY, ',') CNTY 
             CROSS APPLY DBO.SplitString(COMPANY, ',') CPNY

结果

​