分拆格式化字符串为表记录

最新推荐文章于 2023-12-12 19:34:37 发布

arangly

最新推荐文章于 2023-12-12 19:34:37 发布

阅读量440

点赞数

分类专栏： SQL 文章标签： table insert function c

本文链接：https://blog.csdn.net/arangly/article/details/7940557

版权

SQL 专栏收录该内容

32 篇文章 0 订阅

订阅专栏

--引用 Select * From Split('1,2,3' , ',')
--引用 Select * From Split('我,是,www.csdn.com' , ',')
CREATE   FUNCTION [dbo].[Split] (@c VARCHAR(MAX) ,
                                 @split VARCHAR(50))
RETURNS @t TABLE (col VARCHAR(50))
AS
    BEGIN
      WHILE (CHARINDEX(@split,@c)<>0)
        BEGIN
          INSERT @t (col) VALUES (SUBSTRING(@c,1,CHARINDEX(@split,@c)-1))
          SET @c=STUFF(@c,1,CHARINDEX(@split,@c),'')
        END
      INSERT @t (col) VALUES (@c)
      RETURN
    END

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

ALTER FUNCTION [dbo].[FN_GET_OUTSOURCE_PROCESS] (@OUT_PROCESS NVARCHAR(100),@RECEIVE_POINT NVARCHAR(20))
RETURNS @OUTSOURCE_PROCESS_TABLE TABLE(
SEQ_ID      INT,
OUT_CURR_PROCESS NVARCHAR(20),
OUT_NEXT_PROCESS NVARCHAR(20))
AS
BEGIN
   DECLARE @I INT,@J INT,@K INT,@SEQ_ID INT
   DECLARE @OUT_CURR_PROCESS NVARCHAR(20),@OUT_NEXT_PROCESS NVARCHAR(20)

      SET @I=1
   SET @SEQ_ID=1
      SET @J=CHARINDEX(';', @OUT_PROCESS, @I)

      WHILE @J <> 0
        BEGIN
            SET @OUT_CURR_PROCESS=SUBSTRING(@OUT_PROCESS, @I, @J - @I)
            SET @K=CHARINDEX(';', @OUT_PROCESS, @J + 1)

            IF @K = 0
              SET @OUT_NEXT_PROCESS=@RECEIVE_POINT
            ELSE
              SET @OUT_NEXT_PROCESS=SUBSTRING(@OUT_PROCESS, @J + 1, @K - @J - 1)

INSERT INTO @OUTSOURCE_PROCESS_TABLE(SEQ_ID,OUT_CURR_PROCESS,OUT_NEXT_PROCESS)
VALUES(@SEQ_ID,@OUT_CURR_PROCESS,@OUT_NEXT_PROCESS);

            SET @I=@J + 1
      SET @SEQ_ID=@SEQ_ID+1
            SET @J=CHARINDEX(';', @OUT_PROCESS, @I)
        END;

RETURN;

END

DECLARE @#WIP_TEMP_TABLE TABLE(JOB_ORDER_NO NVARCHAR(20),CUSTOMER_NAME NVARCHAR(50),ORDER_QTY INT,BPDATE SMALLDATETIME)

INSERT INTO @#WIP_TEMP_TABLE
     (JOB_ORDER_NO,CUSTOMER_NAME,ORDER_QTY,BPDATE)
EXEC GET_WIP_DATA @FACTORY_CD,
                  @JOB_ORDER_NO,
                  @OUT_CURR_PROCESS,
                  @COLOR_CD,
                  @SIZE_CD,
                  @PROD_LINE,
                  @BUNDLE_NO;