SQL Server How to not execute all code, only the beginning with the DECLARE of variables and the end of the procedure?

jvidinwx  于 2023-11-16  发布在  其他
关注(0)|答案(1)|浏览(98)

I have a code in SQL that do a lot of stuff and I am testing a part in the end of the procedure

The problem is that there is some variables in the beginning and they're used in the end, I would like to not execute all of the code because it takes some time processing, it seams like this:

--Some variables are created and manipulated

DECLARE @DT DATE
SET @DT = ISNULL(@DT, GETDATE())
DECLARE @DF DATE = EOMONTH(@DT)
      , @DI DATE = DATEADD(DAY, 1, EOMONTH(@DT, -1))
DECLARE @DTI DATETIME = @DI
      , @DTF DATETIME = @DF
SET @DTF += 1

/*
    slow proccess
*/

    --In the end the same variables are reused

SELECT *
FROM SYS.TABLES
WHERE create_date BETWEEN @DTI AND @DTF

I can reuse the declare, like this:

--Some variables are created and manipulated

DECLARE @DT DATE
SET @DT = ISNULL(@DT, GETDATE())
DECLARE @DF DATE = EOMONTH(@DT)
      , @DI DATE = DATEADD(DAY, 1, EOMONTH(@DT, -1))
DECLARE @DTI DATETIME = @DI
      , @DTF DATETIME = @DF
SET @DTF += 1

/*
    slow proccess
*/

    --In the end the same variables are reused

DECLARE @DT DATE
SET @DT = ISNULL(@DT, GETDATE())
DECLARE @DF DATE = EOMONTH(@DT)
      , @DI DATE = DATEADD(DAY, 1, EOMONTH(@DT, -1))
DECLARE @DTI DATETIME = @DI
      , @DTF DATETIME = @DF
SET @DTF += 1

SELECT *
FROM SYS.TABLES
WHERE create_date BETWEEN @DTI AND @DTF

It works, but it is ugly, is there another better way?

2w3rbyxf

2w3rbyxf1#

You can replace variables in final select with expression. Something like this:

SELECT *
FROM SYS.TABLES
WHERE create_date BETWEEN DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) AND DATEADD(day,1,EOMONTH(GETDATE()))

相关问题