Possible to tell whether function was passed a value or used default value

Question

Possible to tell whether function was passed a value or used default value

BiatchCakes

SSC Enthusiast

Points: 119
More actions
April 22, 2015 at 6:11 am

#307210

Good morning all,
I have a function that accepts a date parameter and uses getdate() as its default value. If a date is passed in, I'm going to have to find records using the datediff method based on input. If no date is passed, I am going to bypass the datediff logic and search for records based on a column called "is_current" which will reduce the query time.
However, I don't know how to tell if the date value in the function came from an input or was the default. Does anyone have a method to do this?
Thanks a million,
M

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

well functions with an optional parameter, still have to have either a value or the KEYWORD specifically DEFAULT passed.

unlike procedures, you cannot just leave a parameter off of the call.

so you might have an invalid expectation:

IF OBJECT_ID('[dbo].[pr_test]') IS NOT NULL

DROP FUNCTION [dbo].[pr_test]

GO

CREATE function pr_test (@Somedate datetime = NULL)

returns varchar(200)

AS

BEGIN

SET @Somedate = ISNULL(@Somedate,getdate());

return CONVERT(VARCHAR,@Somedate,112) + '-' + CONVERT(VARCHAR,@Somedate,114)

END

SELECT dbo.pr_test() --Error: Msg 313, Level 16, State 2, Line 1 An insufficient number of arguments were supplied for the procedure or function dbo.pr_test.

SELECT dbo.pr_test(DEFAULT)

SELECT dbo.pr_test(NULL)

SELECT dbo.pr_test('2014-12-11')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

BiatchCakes SSC Enthusiast Points: 119 More actions · Answer 2

Lowell - thank you. You are correct. I was going on the assumption that it would behave like a stored procedure.

Out of curiousity, is it possible within stored procedures to check within a stored procedure?

I appreciate your advice.

-m

Lowell SSC Guru Points: 323518 More actions · Answer 3

Inside the stored procedure or function itself, yes, since you are assigning the value, you can log it, do additional logic because it was null or whatever.

SELECT @Somedate = ISNULL(@Somedate,BusinessDate) From SomeTable WHERE is_current=1;

outside of the procedure, can you tell if of 100 calls to a procedure, 34 used the default abd 76 didn't?,

the answer is no.

SQL does not keep track of the explicit calls/parameters. i think a trace or extended event would have the ability to capture parameter values, but you'd have to put that in place, since it doesn't exist by default.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

Lowell (4/22/2015)
SELECT dbo.pr_test(DEFAULT)

OK now. That's about the coolest thing I've seen today. Didn't know you could do that.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St