Variables in Proceedures
-
Is is possible to add variables to functions in SQL Server. My issue is I am buildint a dashboard and I am sendint the variables directly from the dropdown menu. what I need to do is have the ability to modify the Datediff function. By changing the type. i.e datediff(@dateRange, getdate())=0 I need to have the @dateRange be a variable instead of (dd, ww, or yy). Any Ideas? thanks
-
Is is possible to add variables to functions in SQL Server. My issue is I am buildint a dashboard and I am sendint the variables directly from the dropdown menu. what I need to do is have the ability to modify the Datediff function. By changing the type. i.e datediff(@dateRange, getdate())=0 I need to have the @dateRange be a variable instead of (dd, ww, or yy). Any Ideas? thanks
oskardiazdeleon wrote:
i.e datediff(@dateRange, getdate())=0
That's not a legal call to DATEDIFF[^] See the linked documentation for what you actually need.
oskardiazdeleon wrote:
Any Ideas?
CASE @dateRange WHEN 'dd' THEN DATEDIFF(dd, GETDATE(), @someOtherDate)
WHEN 'ww' THEN DATEDIFF(ww, GETDATE(), @someOtherDate)
WHEN 'yy' THEN DATEDIFF(yy, GETDATE(), @someOtherDate)
ELSE 0 ENDYou could then potentially wrap this in a function[^], however, since
GETDATE()is non-deterministic it isn't possible to do what you want, but you could always passGETDATE()into the function. e.g.CREATE FUNCTION DateDifference(@dateRange CHAR(2), @startDate DATETIME, @endDate DATETIME)
RETURNS INT
AS
BEGIN
RETURN CASE @dateRange WHEN 'dd' THEN DATEDIFF(dd, @startDate, @endDate)
WHEN 'ww' THEN DATEDIFF(ww, @startDate, @endDate)
WHEN 'yy' THEN DATEDIFF(yy, @startDate, @endDate)
ELSE 0 END
ENDIt can be called like this:
SELECT dbo.DateDifference(@dateRange, @someDate, GETDATE())
Does this help?
*** Developer Day 4 in Reading, England on 2nd December 2006 - Registration Now Open *** Upcoming Scottish Developers events: * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog