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

可以使用基于集合的插入替换整个光标。我还要提醒你不要使用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...

尝试使用 CROSS APPLY

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

后果