sql基础_一个典型的存储过程

--存储过程
IF OBJECT_ID('spWII_Load','P') IS NOT NULL  
  DROP PROCEDURE [dbo].[spWII_Load]
GO 
SET  ANSI_NULLS ON;
GO
SET  QUOTED_IDENTIFIER ON;
GO

/* =======================
    描    述：在代码中启动事务 创建临时表, 然后在存储过程中处理临时表
             设计了  计数 ,临时表, 快速创建临时表, 插入表的方法, 更新表的方法,字符串格式化 等
    创 建 人：
    创建日期：
    说明: 调用本存储过程必须先建临时表 #tmp_TCTdetail ,并存入明细数据
    DECLARE @ResVal INT = -1;
    EXEC spWMS_LoadCarTranSubmit 'HK', '1', '1', '1', @ResVal OUTPUT
    select @ResVal
-- =======================*/
CREATE PROCEDURE [dbo].[spWII_Load]
  @CompanyID VARCHAR(20), @StockID VARCHAR(20), @PersonnelID VARCHAR(20), @Operator VARCHAR(20), @ResVal INT OUTPUT
AS
  BEGIN
    DECLARE @MaxBatchNo INT = 0;

    EXEC spCRM_GetMaxNum @ComCode = @CompanyID, @ObjID = 'WMS_LoadCarTranBatchNo', @MaxNum = @MaxBatchNo OUTPUT

    IF OBJECT_ID('tempdb.dbo.#tmp_TCTdetail') IS NULL
      BEGIN
        SET @ResVal = -1; --必须先建临时表再调用本存储过程

        RETURN;
      END

    --1,更新 seq
    UPDATE a
    SET    a.Seq = b.rowno
    FROM   #tmp_TCTdetail a
           INNER JOIN (SELECT row_number() OVER (ORDER BY getdate()) rowno, * FROM #tmp_TCTdetail) b
             ON a.BoxID = b.BoxID
             AND a.WaveBillNo = b.WaveBillNo
             AND a.SourceBillNo = b.SourceBillNo
             AND a.SourceBillTypeID = b.SourceBillTypeID

    --2,更新batchNo
    UPDATE #tmp_TCTdetail
    SET    BatchNo = @CompanyID + right(10000000000 + @MaxBatchNo, 10)

    --3,更新logid
    --3.1 得到主表
    SELECT   Cast('' AS VARCHAR(100)) LogID, WaveBillNo, SourceBillNo, SourceBillTypeID
    INTO     #tmp_TCTmaster
    FROM     #tmp_TCTdetail
    GROUP BY WaveBillNo, SourceBillNo, SourceBillTypeID

    --3.2 更新主表logid
    DECLARE @MaxLogID INT = 0;
    DECLARE @masterCount INT; --主表记录数
    SELECT @masterCount = count(logid) FROM #tmp_TCTmaster;
    EXEC spCRM_GetMaxNum @ComCode = @CompanyID, @ObjID = 'WMS_LoadCarTran', @AddNum = @masterCount, @MaxNum = @MaxLogID OUTPUT

    UPDATE a
    SET    a.LogID = @CompanyID + right(10000000000 + @MaxLogID + b.rowno - 1, 10)
    FROM   #tmp_TCTmaster a
           INNER JOIN (SELECT row_number() OVER (ORDER BY WaveBillNo) rowno, * FROM #tmp_TCTmaster) b
             ON a.WaveBillNo = b.WaveBillNo
             AND a.SourceBillNo = b.SourceBillNo
             AND a.SourceBillTypeID = b.SourceBillTypeID

    --3.3 插入主表
    INSERT INTO Wms_MaterialLoadCardTranMaster(CompanyID, LogID, BillTypeID, StockID, WaveBillNo, SourceBillNo, SourceBillTypeID,
                  PersonnelID, Operator, ModifyDTM, TaskStatus, Driver, CarCode, TransPers)
      SELECT @CompanyID, LogID, 'WMS_LoadCarTran', @StockID, WaveBillNo, SourceBillNo, SourceBillTypeID, @PersonnelID, @Operator,
             getdate(), '1', '', '', ''
      FROM   #tmp_TCTmaster

    --3.5 用主表logid 更新 明细表
    UPDATE a
    SET    a.LogID = b.LogID
    FROM   #tmp_TCTdetail a
           INNER JOIN #tmp_TCTmaster b
             ON a.WaveBillNo = b.WaveBillNo
             AND a.SourceBillNo = b.SourceBillNo
             AND a.SourceBillTypeID = b.SourceBillTypeID

    --3.6 插入明细表
    INSERT INTO Wms_MaterialLoadCardTranDetail(CompanyID, LogID, BoxID, BoxCode, Qty, ModifyDTM, Seq, Driver, CarCode, TransPers,
                  BatchNo                      )
      SELECT @CompanyID, LogID, BoxID, BoxCode, Qty, getdate(), Seq, Driver, CarCode, TransPers,
             BatchNo
      FROM   #tmp_TCTdetail

    DROP TABLE #tmp_TCTmaster

    DROP TABLE #tmp_TCTdetail

    SET @ResVal      = 1
  END

-- ======================= 存储过程的使用 =======================
  --从存储过程初始化临时表
  CREATE TABLE #storerSizeInv(
    StorerID VARCHAR(100), StorerCode VARCHAR(100), StorerTypeID VARCHAR(100), CardID VARCHAR(100), KindID VARCHAR(100),
    SeriesID VARCHAR(100), ModelID VARCHAR(100), ItemID VARCHAR(100), SeasonID VARCHAR(100), MaterialID VARCHAR(100),
    SizeID VARCHAR(100), CanMiscible VARCHAR(100), ExtentCode VARCHAR(100), StorerQty INT, Cubage VARCHAR(100),
    CurUsed VARCHAR(100), RowOver VARCHAR(100))

  --这里获取到 #storerSizeInv 中的  CardID, KindID, ModelID, SeasonID 都是储位的属性,本程序中没有用到
  INSERT INTO #storerSizeInv
  EXEC        spWms_Inv_xxx @CompanyID, @StockID, '', @SubAreaIDS

 拆分字符串的存储过程

使用举例：exec spSplitString  'Hello,World',','

alter PROC spSplitString
  @string VARCHAR(128), @delimiter VARCHAR(128)
AS
WITH
  Split(stpos, endpos)
  AS
    (SELECT 0 AS stpos, CHARINDEX(@delimiter, @string) AS endpos
     UNION ALL
     SELECT endpos + LEN(@delimiter), CHARINDEX(@delimiter, @string, endpos + LEN(@delimiter))
     FROM   Split
     WHERE  endpos > 0)
SELECT ROW_NUMBER() OVER (ORDER BY stpos) AS ID, SUBSTRING(@string, stpos, CASE WHEN endpos = 0 THEN LEN(@string) - stpos + 1 ELSE endpos - stpos END) AS Value
FROM   Split

CREATE FUNCTION dbo.SplitString(@string NVARCHAR(MAX), @delimiter CHAR(1))
  RETURNS TABLE
AS
RETURN (WITH
          Split
          AS
            (SELECT cast(0 AS BIGINT) AS stpos, CHARINDEX(@delimiter, @string) AS endpos
             UNION ALL
             SELECT endpos + LEN(@delimiter), CHARINDEX(@delimiter, @string, endpos + LEN(@delimiter))
             FROM   Split
             WHERE  endpos > 0)
        SELECT ROW_NUMBER() OVER (ORDER BY stpos) AS ID, SUBSTRING(@string, stpos, CASE WHEN endpos = 0 THEN LEN(@string) - stpos + 1 ELSE endpos - stpos END) AS Value
        FROM   Split);
GO

SELECT * FROM SplitString('Hello,World,baozi,good', ',')