SQL Procedure with defined Paramters not producing results

1

Hi All,
I am trying to run a Procedure using defined variables and it's not producing any results.
I have data in my table for 6/18/2019 and 6/19/2019 so I am expecting that data to pull through.
It's been a while since I have written one of these type of procedures.
As always any help is appreciated.

--CREATE PROCEDURE [dbo].[RMDB_Rejections_Summary] AS

DECLARE @Date1 DATE,
@Date2 DATE

SET @Date1 = (SELECT MAX(CAST([LOAD_DTM] AS DATE)) FROM [dbo].[RMDB_Rejections])
SET @Date2 = (SELECT MAX(CAST([LOAD_DTM] AS DATE)) FROM [dbo].[RMDB_Rejections] WHERE CAST([LOAD_DTM] AS DATE) < @Date1)

SELECT [ERR_ELMNT_TYPE_CD]
,[ERR_ELMNT_NM]
,[ENRLMNT_ERR_ELMNT_ERR_MSG_TXT]
,[TOTAL_COUNT]
,[CATEGORY]
,[LOAD_DTM]
FROM [dbo].[RMDB_Rejections]
WHERE CAST([LOAD_DTM] AS DATE) BETWEEN @Date1 AND @Date2

2

@Date2 is before @Date1 (WHERE CAST([LOAD_DTM] AS DATE) < @Date1), so will not work with the BETWEEN as it stands

DROP TABLE IF EXISTS #WIBBLE
CREATE TABLE #WIBBLE (
	wobble DATE
)

INSERT INTO #WIBBLE
VALUES ('20190618'), ('20190619')

SELECT * FROM #WIBBLE

DECLARE @DATE1 DATE = '20190619'
DECLARE @DATE2 DATE = '20190618'

-- THIS WONT WORK, DATE2 IS BEFORE DATE1
SELECT * FROM #WIBBLE
WHERE wobble BETWEEN @DATE1 AND @DATE2

-- this works
SELECT * FROM #WIBBLE 
WHERE wobble BETWEEN @DATE2 AND @DATE1

-- MY PREFERED, writing it makes you think about how the @Dates are to be used
SELECT * FROM #WIBBLE
WHERE wobble >= @DATE2 AND wobble <= @DATE1
3

Thanks uberbloke that was the issue I had the @Date2 and @Date1 in the wrong order in my where clause. Below resolved the issue.

WHERE CAST([LOAD_DTM] AS DATE) BETWEEN ISNULL(@Date2,CAST(GETDATE() AS DATE)) AND ISNULL(@Date1,CAST(GETDATE() AS DATE))