mardi 3 février 2015

Can I implement Intellisense into my stored procedure with custom constants?


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?





Aucun commentaire:

Enregistrer un commentaire