Join with udf - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Join with udf

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-17 : 04:14:31

Hai,
can we use join with the udf.
meaning, can i join my table result with udf result.
if so, how..

1. how can i sum the char column value
say,
producttable

itemcode = '12345'(varchar20)
how can i sum the column. i want to sum each column.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-17 : 04:58:35

select sum(char_col*1) from your_table

Explain how you want to join with result of udf

Madhivanan

Failing to plan is Planning to fail

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-17 : 05:57:58

declare @product table
(id int,
model varchar(10))

insert into @product values (1,'11111')
insert into @product values(2,'11112')
insert into @product values (3,'11113')

select * from @product
select sum(model*1) from @product

hai i want the output as
1 5
2 6
3 7
how to get it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 10:23:03

[code]
CREATE FUNCTION AddDigits (@StrVal varchar(20))
RETURNS int
AS
DECLARE @Sum int,@Digit int

WHILE @StrVal >''
BEGIN
SELECT @Digit=LEFT(@StrVal,1), @StrVal = SUBSTRING(@StrVal,2,LEN(@StrVal))
SET @Sum=COALESCE(@Sum,0)+@Digit

END

RETURN @Sum
END

then use udf like this

SELECT id,dbo.AddDigits(model)
FROM @product
[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-18 : 00:12:13

[code]
try this one too
declare @product table
(id int,
model varchar(10))

insert into @product values (1,'11111')
insert into @product values(2,'11112')
insert into @product values (3,'11113')
insert into @product values (4,'11117')

select id,sum(cast(substring(model,number,1) as int))
from @product p
inner join master..spt_values as m on m.type = 'p'
where len(model) >= number
group by id
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources