I created a stored proc to do some cleanup on some orphaned temp tables by issuing a DROP. However, I want to vary the time threshold so I have a stored procedure that accepts a time unit (e.g. hours, minutes, days - as varchar(25)) and the number of said units (1,2,3.. - as int).

So in my code, I have hard-coded varchar expressions to call the correct DATEADD function:

IF @TimeOffsetUnit = 'Hour'
    SET @CutoffDateTime = DATEADD(hh, @UnitAmount * -1, GETDATE())

IF @TimeOffsetUnit = 'Day'
    SET @CutoffDateTime = DATEADD(dd, @UnitAmount * -1, GETDATE())

IF @TimeOffsetUnit = 'Minute'
    SET @CutoffDateTime = DATEADD(mi, @UnitAmount * -1, GETDATE())

Obviously, when I call my procedure, I need to know in advance to send in the parameter 'Hour' not 'Hours' otherwise the code will not execute the intended statement. If someone besides myself wants to use this procedure, they will undoubtedly need to examine the code to get the parameter constant spelling correct.

Is there a way, like DATEADD, where you start typing in a valid value and have Intellisense provide a dropdown of acceptable values for a stored procedure? Or am I expecting too much from SQL Server?

  • 2
    How about you throw user friendly error using RAISEERROR or PRINT saying 'Valid input parameters for @TimeOffsetUnit are Hour, Day or Minute' ? Or am I expecting too much from SQL Server? Yes you are :-) – Kin Shah Feb 3 '15 at 22:35
  • Or use a CASE to account for variations from users. But I think @Kin's idea is best. – LowlyDBA Feb 3 '15 at 22:38
  • 2
    We usually build ours with an optional help parameter, which short circuits and prints a list of parameters and expected inputs. – Unicorno Marley Feb 3 '15 at 23:46
  • The stored procedure code itself would be better (more self-documenting) if you didn't use lazy shorthand for dateadd arguments. Is typing HOUR, DAY and MINUTE there so hard? – Aaron Bertrand Feb 4 '15 at 3:51
  • Unicorno: That is a fantastic idea. Thanks. – Queue Mann Feb 4 '15 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.