vendredi 20 juillet 2018

Raise Error/ Throw Exception for Certain Condition SQL Server

http://www.sqlfiddle.com/#!18/c913b/1

CREATE TABLE Table1 (
[ID] int PRIMARY KEY,
[Date] date,
[Name] varchar(40)
);

INSERT INTO Table1 (ID, date, Name )
Values ('1','07-20-18','Fred'),
('2','07-15-18','Sam'),
('3','07-20-18','Ben'),
('4','07-19-18','Simon'),
('5','07-25-18','Dave');

Current Query:

DECLARE @StartDate AS DATETIME,
@CurrentDate AS DATETIME
SET @StartDate = GETDATE() -30
SET @CurrentDate = GETDATE()
SELECT [ID], [Date], [Name]
FROM Table1
WHERE [Date] BETWEEN @StartDate AND @CurrentDate

Current result:

| ID |       Date |  Name |
|----|------------|-------|
|  1 | 2018-07-20 |  Fred |
|  2 | 2018-07-15 |   Sam |
|  3 | 2018-07-20 |   Ben |
|  4 | 2018-07-19 | Simon |

This query compares with the range of day set for the @startdate variable. This can be changed to anything by anyone who wants to run the query. The issue is if someone selects a really large range of dates it crashes. Is there a way i can set an if statement that throws an error message if for example they tried to run a query for longer than 100 days.

Result: ERROR - Please lower the date range

Is this possible at all?

Aucun commentaire:

Enregistrer un commentaire