切割傳入參數的資料表值函式

最近從某人力銀行的電子表單上看到有趣的用法，
需要使用預存程序，又需要傳入很多個參數時，
這個方法還滿有趣的。
傳給預存程序時，只有傳入一個參數，
靠著函數的運用，將參數一一取出，
再給預存程序使用。

圖片來源Designed by Freepik

---

首先新增今天的主角，可切割字串的資料表值函式。
USE [DBName]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_SYS_Split](
   @String nvarchar (4000),
   @Delimiter nvarchar (10)
   )

  returns @ValueTable table ([Value] nvarchar(4000),[RowNumber] INT)
  begin
     declare @NextString nvarchar(4000)
     declare @Pos int
    declare @NextPos int
    declare @CommaCheck nvarchar(1)
    declare @rNumber int

    --Initialize
    set @rNumber = 0
    set @NextString = ''
    set @CommaCheck = right(@String,1)
    set @String = @String + @Delimiter

    --Get position of first Comma  
    set @Pos = charindex(@Delimiter,@String)
    set @NextPos = 1
 
    --Loop while there is still a comma in the String of levels
    while (@pos <>  0)
    begin
      set @rNumber = @rNumber + 1;
      set @NextString = substring(@String,1,@Pos - 1)
      insert into @ValueTable ( [Value],[RowNumber]) Values (@NextString,@rNumber)
      set @String = substring(@String,@pos +1,len(@String))
      set @NextPos = @Pos
      set @pos  = charindex(@Delimiter,@String)
    end
    return
  end

這段資料表值函式，在預存程序中的取用方法如下。
範例中假設傳入預存程序的參數包含兩個值（開始日期及停止日期），
然後兩個值是以「^」上箭頭為分隔符號。
看看以下的程式就知道怎麼將兩個字串取出來實際應用了。
USE [「DBName]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[sp_yourName]
  @FormValue as NVARCHAR(4000)
AS
BEGIN
  --創建TEMPTABLE
  DECLARE @ReturnTable TABLE (EMPLOYEE_ID INT,EMPLOYEE_NAME NVARCHAR(50));

  -- 開始日期
  declare @StartDate as varchar(10)
  -- 開始時間
  declare @StartTime as varchar(4)
  SELECT @StartDate = [Value] FROM fn_SYS_Split(@FormValue,'^') WHERE [RowNumber] = 1;
  SELECT @StartTime = [Value] FROM fn_SYS_Split(@FormValue,'^') WHERE [RowNumber] = 2;

  INSERT INTO @ReturnTable (EMPLOYEE_ID,EMPLOYEE_NAME)
  select MEMBER_ID, MEMBER_NAME from [table] where col1 = @StartDate and col2 = @StartTime  --SQL指令

  --回傳簽核資料
  SELECT * FROM @ReturnTable;
END

其實電子表單的用法是在前端網頁使用ajax呼叫function，
然後將表單上的資料傳回給預存程序。
於是在前端網頁先將個資料以「^」分隔後組合起來，
預存程序只要接收一個字串資料。

為什麼要這樣做？
我也不知道。只是覺得滿有趣的，
寫程式多些想像力，可以有很多種玩法啦！