default values not working for SP? 2

[1DMF]

Hi,
I have the following SP

PROCEDURE [dbo].[spADM_RiskData] 

	@MemNo varchar(12) = '%', 
	@StartDate char(10) = NULL,
	@EndDate char(10) = NULL
	
AS
BEGIN

	SET NOCOUNT ON;
	
	-- Set default dates
	Set @StartDate = coalesce(@StartDate, '2008-01-01') 
	Set @EndDate = coalesce(@EndDate, Convert(char(10), GetDate(), 121))

	SELECT Case_ID,Adv_MemNo,Reason
	FROM Case_Checking INNER JOIN Business_Register
	ON Case_Checking.Case_ID = Business_Register.Rec_ID
	WHERE (Flag_Date BETWEEN @StartDate AND @EndDate) AND Adv_MemNo LIKE @MemNo + '%'
	
END

I am calling it with the following...

spADM_RiskData '','',''

Which should trigger the default values (shouldn't it?)

But I seem to be getting zero records returned instead of all of them?

If I use

spADM_RiskData '','2008-01-01','2013-03-06'

I get the expected returned records.

So what's wrong with the empty strings for the date parameters and why isn't it triggering the default values?

Thanks,

1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[Rhys666]

if you call the proc with;

spADM_RiskData '','',''

You are not passing NULL values to the second and third parameters but empty string values which are not the same. Try;

spADM_RiskData ''

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[Rhys666]

NB: by '...passing NULL values to the second and third parameters...' I mean that you are in fact passing values when you need to pass in no value at all to utilise the defined defaults. As an aside, any reason you're using COALESCE instead of ISNULL?...

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[1DMF]

but how do you then pass in an end date but no start date?

I've tried

spADM_RiskData '',,'2009-01-01'

But that just errors with incorrect syntax?

As an aside, any reason you're using COALESCE instead of ISNULL?...

Yes, from my other thread

http://tek-tips.com/viewthread.cfm?qid=1705733

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[SQLScholar]

Easiest way is to name the parameters when you call it. See here:

http://geekswithblogs.net/whiletrue...onal-parameters-in-sql-stored-procedures.aspx

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLScholar]

Or if people want the MS version:

http://msdn.microsoft.com/en-gb/library/ms189330(v=sql.105).aspx

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[1DMF]

Thanks SQLScholar,

Never realised you could pass in "@varname = 'value'", that makes it a whole lot easier, I always thought you had to comma separate and they had to be in the order you delcared them in the SP!

I resolved the issue in my Catalyst perl app by using

    # Build SP string
    my $sp = "spADM_RiskData '$memno',";
    $sp .= (defined $start_date)? "'$start_date'," : "'2008-01-01'";
    $sp .= (defined $end_date)? ",'$end_date'" : '';

But I think I will refactor to correcly use name=value!

Regards,
1DMF



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[1DMF]

I refactored to this

    # Build SP string
    my $sp = '@MemNo = ' . "'$memno'";
    $sp .= (defined $start_date)? ',@StartDate = ' . "'$start_date'" : '';
    $sp .= (defined $end_date)? ',@EndDate = ' . "'$end_date'" : '';

It's working grand!

Much obliged.

1DMF.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[DaveInIowa]

You could also use the keyword [bold]DEFAULT[/bold] as placeholders

spADM_RiskData DEFAULT, DEFAULT,'2009-01-01'

 

[1DMF]

cool, thanks for the info.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads