【SQL Server】ストアドプロシージャでGETDATE()複数回実行

◆コード①：ストアドプロシージャ内で複数回実行

CREATE PROC [dbo].[PROC1]
AS
BEGIN
  SELECT GETDATE();
  WAITFOR DELAY '00:00:01';
  SELECT GETDATE();
END
GO

EXEC [dbo].[PROC1]

◆結果①

　

◆コード②：1つのSELECT内で複数回実行（重い処理を挟む）

-- データ作成
CREATE TABLE tb_tmp (col1 INT, col2 INT);

DECLARE @i INT = 0, @loop_count INT= 100000;

WHILE @i <= @loop_count
BEGIN
  INSERT tb_tmp VALUES(@i/10,@i)
  SET @i += 1
END
GO

CREATE PROC [dbo].[PROC1]
AS
BEGIN

SELECT GETDATE();
-- GETDATA()を複数と重い処理を含むSELECTを実行
SELECT date1, date2, GETDATE() AS date3
  FROM (
    SELECT date1, GETDATE() AS date2
      FROM (SELECT GETDATE() AS date1) AS a
      CROSS JOIN (
        tb_tmp AS b FULL OUTER JOIN
        tb_tmp AS c ON b.col1 = c.col1 -- 重い結合
      )
  ) AS d
  CROSS JOIN
  tb_tmp  -- 重い結合
  GROUP BY date1, date2
;
SELECT GETDATE();
END
GO

EXEC [dbo].[PROC1]

◆結果②
　

◆コード③：1つのinsert内で複数回実行（1000行insert）

CREATE TABLE dbo.tb_tmp (col1 INT, col2 DATETIME);
GO

CREATE PROC [dbo].[PROC1]
AS
BEGIN
  INSERT dbo.tb_tmp (col1, col2) VALUES
     (0,GETDATE()),(1,GETDATE()),(2,GETDATE()),(3,GETDATE()),(4,GETDATE()),(5,GETDATE()),(6,GETDATE()),(7,GETDATE()),(8,GETDATE()),(9,GETDATE())
    ,(0,GETDATE()),(1,GETDATE()),(2,GETDATE()),(3,GETDATE()),(4,GETDATE()),(5,GETDATE()),(6,GETDATE()),(7,GETDATE()),(8,GETDATE()),(9,GETDATE())
    ,(0,GETDATE()),(1,GETDATE()),(2,GETDATE()),(3,GETDATE()),(4,GETDATE()),(5,GETDATE()),(6,GETDATE()),(7,GETDATE()),(8,GETDATE()),(9,GETDATE())
    ,(0,GETDATE()),(1,GETDATE()),(2,GETDATE()),(3,GETDATE()),(4,GETDATE()),(5,GETDATE()),(6,GETDATE()),(7,GETDATE()),(8,GETDATE()),(9,GETDATE())
    ,(0,GETDATE()),(1,GETDATE()),(2,GETDATE()),(3,GETDATE()),(4,GETDATE()),(5,GETDATE()),(6,GETDATE()),(7,GETDATE()),(8,GETDATE()),(9,GETDATE())


＜中略＞

    ,(0,GETDATE()),(1,GETDATE()),(2,GETDATE()),(3,GETDATE()),(4,GETDATE()),(5,GETDATE()),(6,GETDATE()),(7,GETDATE()),(8,GETDATE()),(9,GETDATE())
    ,(0,GETDATE()),(1,GETDATE()),(2,GETDATE()),(3,GETDATE()),(4,GETDATE()),(5,GETDATE()),(6,GETDATE()),(7,GETDATE()),(8,GETDATE()),(9,GETDATE())
    ,(0,GETDATE()),(1,GETDATE()),(2,GETDATE()),(3,GETDATE()),(4,GETDATE()),(5,GETDATE()),(6,GETDATE()),(7,GETDATE()),(8,GETDATE()),(9,GETDATE())
  ;

  select distinct(col2) from dbo.tb_tmp;

◆結果③