SQL variable restriction

timberadmin
timberadmin used Ask the Experts™
on
I'm creating a stored proc with a variable @AsOfDate as date. Now, I only want to accept the input date if it is within 3 days of today, otherwise it should not accept it and throw an error. Currently I'm doing this with a IF/ELSE statement. Is there a better way of handling this?

I'm using SQL 2012
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Something like...
CREATE PROC your_proc (@AsOfDate date) AS

IF ABS(DATEDIFF(day,@AsOfDate, CAST(GETDATE() as date)))  > 3  
   begin
   SELECT 'error'
   end 
ELSE
   begin
   SELECT 'your proc goes here'
   end

Open in new window

Author

Commented:
So that's exactly how I'm doing it right now. I assume this is the correct and most efficient way of handling it?
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
I'm sure there are other ways, but this is the way I'd do it.

btw Did you really want to throw an error, which would involve RAISEERROR, or just handle it?

Author

Commented:
If this stored proc is run from Excel VBA, I want the user to see the error message I give.
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017
Commented:
Then I think it's better you raise an SQL Server error. Something like this:

RAISERROR (15999,-1,-1, 'The date difference is higher than 3 days');

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial