Returning Single Value from UDF

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 2000 Forums

SQL Server Development (2000)

Returning Single Value from UDF

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-06-02 : 12:40:51

Hi,

I have a UDF called agegrp1 which contains the following statements:


DECLARE @AgeGroup char(10)--Age in years.

If @Age >= 0 and @Age <= 12
 set @AgeGroup = '00 - 12'

Return @AgeGroup

I invoke this function from Query Analyser:


select dbo.agegrp1(4)

and get 0.

As near as I can tell I am returning the first value from my assignment statement. So if I place @AgeGroup = 'q0 - 12' I get back q.

What am I doing wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-02 : 12:49:56

Could you post the entire UDF code so that we may copy it to our machines and try it out there?

Tara

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-06-02 : 13:34:16

Hi,

Sure:


CREATE FUNCTION agegrp1

	(@Age int)


RETURNS varchar
AS



BEGIN

DECLARE @AgeGroup char(10)--Age in years.

If @Age >= 0 and @Age <= 12
 set @AgeGroup = '00 - 12'

If @Age >= 13 and @Age <= 17
 set @AgeGroup = "13 - 17"

If @Age >= 18 and @Age <= 25
 set @AgeGroup = "18 - 25"

If @Age >= 26 and @Age <= 35
 set @AgeGroup = "26 - 35"

If @Age >= 36 and @Age <= 45
 set @AgeGroup = "36 - 45"

If @Age >= 46 and @Age <= 55
 set @AgeGroup = "46 - 55"

If @Age >= 56 and @Age <= 65
 set @AgeGroup = "56 - 65"

If @Age > 65
 set @AgeGroup = "66+"

Return @AgeGroup

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-02 : 13:39:41

You need to change the data type of @AgeGroup.



CREATE FUNCTION agegrp1
(@Age int)

RETURNS varchar(10)
AS

BEGIN

DECLARE @AgeGroup varchar(10)--Age in years.

If @Age >= 0 and @Age <= 12
 set @AgeGroup = '00 - 12'

If @Age >= 13 and @Age <= 17
 set @AgeGroup = '13 - 17'

If @Age >= 18 and @Age <= 25
 set @AgeGroup = '18 - 25'

If @Age >= 26 and @Age <= 35
 set @AgeGroup = '26 - 35'

If @Age >= 36 and @Age <= 45
 set @AgeGroup = '36 - 45'

If @Age >= 46 and @Age <= 55
 set @AgeGroup = '46 - 55'

If @Age >= 56 and @Age <= 65
 set @AgeGroup = '56 - 65'

If @Age > 65
 set @AgeGroup = '66+'

Return @AgeGroup

END

GO

You defined it differently in the declaration and then in the returns.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-02 : 13:46:34

I would Change to A SP and a Case statement like this.

DECLARE @AgeGroup char(10)--Age in years.

Select
@AgeGroup = Case When @Age Between 0 and 12
Then '00 - 12'
When @Age Between 13 and 17
Then '13 - 17'
When @Age Between 18 and 25
Then '18 - 25'
When @Age Between 26 and 35
Then '26 - 35'
end

Jim
Users <> Logic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-06-02 : 16:56:40

Hi,

I changed the data type and that worked, thanks.

I probably will not be able to use a stored procedure because of the logic of my application, I am actually calling this function from another one, but thanks for the suggestion.

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-06-02 : 17:11:21

Hi,

Plus I don't think I can return a value from a SP like I can a function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources