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?
1条答案
按热度按时间2w3rbyxf1#
You can replace variables in final select with expression. Something like this: