Function that calls another function problem

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 that calls another function problem

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-15 : 06:18:05

My original function takes two parameters :

CREATE FUNCTION [dbo].[udf_CustomSum]
(
@CFL_BGC_CODE nvarchar(20),
@Projected_Month int
)
RETURNS DECIMAL (18,2)

now I tried to create an Inline function that will call my original function and will return a table but I get some errors.
Here is what I tried:

CREATE FUNCTION dbo.RowSumBasedOnCode
(
@myRowCode nvarchar(20)
)
RETURNS TABLE
AS

RETURN
(
DECLARE @counter int
set @counter = 1
WHILE @counter < 13
begin
SELECT dbo.udf_CustomSum(@myRowCode, @counter)
end
)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-15 : 06:43:55

some errors - hm...

No, you're never too old to Yak'n'Roll if you're too young to die.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-15 : 06:48:45

Yeah...I was just going to write something along those lines. There are so many errors here I don't even know where to start.

skiabox: I think the best thing for you would be to rewiew this article about functions...you really need to get a better understanding of the concept before doing anything else -> http://www.sqlteam.com/article/user-defined-functions

- Lumbago

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-15 : 07:18:45

Basically I want to return a table with 12 rows.
Any ideas how to achieve that?
Thank you very much!

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-15 : 07:45:51

I tried this code and it seems to work !

USE BUDGET
GO

CREATE FUNCTION dbo.RowSumBasedOnCode
(
@myRowCode nvarchar(20)
)
RETURNS @StringArrayTable TABLE ( January DECIMAL(18,2), February DECIMAL(18,2), March DECIMAL(18,2), April DECIMAL(18,2), May DECIMAL(18,2),
June DECIMAL(18,2), July DECIMAL(18,2), August DECIMAL(18,2), September DECIMAL(18,2), October DECIMAL(18,2),
November DECIMAL(18,2), December DECIMAL(18,2) )

AS

BEGIN
INSERT INTO @StringArrayTable
VALUES ( dbo.udf_CustomSum(@myRowCode, 1), dbo.udf_CustomSum(@myRowCode, 2), dbo.udf_CustomSum(@myRowCode, 3), dbo.udf_CustomSum(@myRowCode, 4),
dbo.udf_CustomSum(@myRowCode, 5), dbo.udf_CustomSum(@myRowCode, 6), dbo.udf_CustomSum(@myRowCode, 7), dbo.udf_CustomSum(@myRowCode, 8),
dbo.udf_CustomSum(@myRowCode, 9), dbo.udf_CustomSum(@myRowCode, 10), dbo.udf_CustomSum(@myRowCode, 11), dbo.udf_CustomSum(@myRowCode, 12) )

RETURN
END

GO

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-15 : 07:58:57

CREATE FUNCTION dbo.RowSumBasedOnCode
(
@myRowCode nvarchar(20)
)
RETURNS @Result Table(customSum decimal(18,2))
AS
BEGIN
DECLARE @counter int
set @counter = 1
WHILE @counter < 13
begin
Insert into @Result SELECT dbo.udf_CustomSum(@myRowCode, @counter)
set @counter = @counter+1
end
Return
END

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-15 : 08:12:24

Nice one sanoj_av but it returns 12 rows.
Can it be altered so that it returns 1 row with 12 columns?
Thnx!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-15 : 08:25:40

quote:
Originally posted by skiabox

Nice one sanoj_av but it returns 12 rows.
Can it be altered so that it returns 1 row with 12 columns?
Thnx!

Apply where clause

Madhivanan

Failing to plan is Planning to fail

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-15 : 08:29:12

quote:
Originally posted by madhivanan

quote:
Originally posted by skiabox

Nice one sanoj_av but it returns 12 rows.
Can it be altered so that it returns 1 row with 12 columns?
Thnx!

Apply where clause

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-15 : 08:30:58

Insert into @Result
SELECT dbo.udf_CustomSum(@myRowCode, @counter)
WHERE........

Madhivanan

Failing to plan is Planning to fail

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-15 : 08:46:08

Skiabox,
I dont know whay do u want to show it in one row instead of in 12 rows. You need to use some delimeter for seperating values and in your example I could not see any columns other than the sum.So uniquely identifying the row is again a problem. any way I hope this will help you in some way.

CREATE FUNCTION dbo.RowSumBasedOnCode
(
@myRowCode nvarchar(20)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Result Table(customSum decimal(18,2))
DECLARE @counter int,@strRes varchar(8000)
set @counter = 1
WHILE @counter < 13
begin
Insert into @Result SELECT dbo.udf_CustomSum(@myRowCode, @counter)
set @counter = @counter+1
end

SELECT @strRes=custom_Sum
FROM (
SELECT DISTINCT customSum
FROM @Result
) q0
CROSS APPLY
(
SELECT STR(customSum) + ','
FROM @Result q1
WHERE q1.customSum = q0.customSum
FOR XML PATH ('')
) Q(custom_Sum)
Return @strRes
END

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-15 : 08:56:01

sanoj_av I tried your last script but it returns only one cell!
I need 1 row with 12 columns(1 column for every @counter)
Thnx anyway for your fast answers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources