Function return a default value instead of null

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Function return a default value instead of null

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-09-16 : 07:22:41

Hello,
I have the following function. Which returns null if the value does not exist. How do i make the function return a default value of '44-1' Instead of null

thanks



ALTER FUNCTION [dbo].[PricePlan_billing]
(
	@User VARCHAR(30)
)
RETURNS VARCHAR(30)
AS
BEGIN
	RETURN	(
			
			SELECT	CAST(DefaultCountry AS VARCHAR(11)) + ',' + PricePlan
			FROM	credits
			WHERE	UserName = @User
		)
END

_____________________

Yes O !

ranganath
Posting Yak Master

209 Posts

Posted - 2008-09-16 : 07:24:39

hi,
Use Isnull

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-09-16 : 07:33:54

I tried, still giving me null

SELECT	isnull((CAST(DefaultCountry AS VARCHAR(11)) + ',' + PricePlan),'234-1')
			FROM	credits
			WHERE	UserName = @User

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 07:34:21

may be this:-

ALTER FUNCTION [dbo].[PricePlan_billing]
(
	@User VARCHAR(30)
)
RETURNS VARCHAR(30)
AS
BEGIN
	RETURN	(
			
			SELECT	COALESCE(CAST(DefaultCountry AS VARCHAR(11)) + ',','') + COALESCE(PricePlan,'')
			FROM	credits
			WHERE	UserName = @User
		)
END

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-09-16 : 07:46:09

still gives me null

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 07:49:57

quote:
Originally posted by mary_itohan

still gives me null

_____________________

Yes O !

do you have a record existing for passed on @user value?

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-09-16 : 07:51:22

no.

Its basically a billing table and if the user hasnt registered with us, then he hasnt got credits.

So we wont make a record for him, until he has credits
Hope clear ?

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 07:53:21

quote:
Originally posted by mary_itohan

no.

Its basically a billing table and if the user hasnt registered with us, then he hasnt got credits.

So we wont make a record for him, until he has credits
Hope clear ?

_____________________

Yes O !

in that case you should use coalesce at place where you call the function. can you post query part where you call it

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-16 : 08:05:34

[code]declare @i varchar(20)
set @i = isnull((select [dbo].[PricePlan_billing]('test')),'44-1')
print @i
[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-16 : 08:17:08

Here is another option:


ALTER FUNCTION [dbo].[PricePlan_billing]
(
	@User VARCHAR(30)
)
RETURNS VARCHAR(30)
AS
BEGIN
	declare @retval varchar(30)
	
	SELECT @retVal = CAST(DefaultCountry AS VARCHAR(11)) + ',' + PricePlan
	FROM	credits
	WHERE	UserName = @User

	return isNull(@retval, '44-1' )
	
END

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 08:25:40

quote:

Originally posted by TG

Here is another option:


ALTER FUNCTION [dbo].[PricePlan_billing]
(
	@User VARCHAR(30)
)
RETURNS VARCHAR(30)
AS
BEGIN
	declare @retval varchar(30)
	
	SELECT @retVal = CAST(DefaultCountry AS VARCHAR(11)) + ',' + PricePlan
	FROM	credits
	WHERE	UserName = @User

	return isNull(@retval, '44-1' )
	
END

Be One with the Optimizer
TG

still better to use COALESCE on top just in case OP wants not null value to be returned when only one of fields has null value.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-16 : 12:56:16

Only the O.P. knows what business rules should be applied if either of the underlying values is NULL.
I was just offering an alternative to:

>>in that case you should use coalesce at place where you call the function.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 13:10:00

quote:
Originally posted by TG

Only the O.P. knows what business rules should be applied if either of the underlying values is NULL.
I was just offering an alternative to:

>>in that case you should use coalesce at place where you call the function.

Be One with the Optimizer
TG

yeah.. thats true

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources