Stored procedure to find sum - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Stored procedure to find sum

Author

Topic

Chandan
Starting Member

9 Posts

Posted - 2006-11-21 : 13:20:10

Stored procedure to get sum of first 5 natural number

I tried the following code but didnt worked

alter PROCEDURE Testfor
@intt int,
@a int
as
set @a=1
while(@a<=@intt)
@a=@a+1

next @a
print @a

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 13:22:18

alter PROCEDURE Testfor
@intt int,
@a int
as
set @a=1
while(@a<=@intt)
@a=@a+1

~~next @a~~
print @a

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-21 : 13:41:09

Try this

alter PROCEDURE Testfor
@intt int
as
declare @a int, @result int
select @result = 0, @a = 1
while(@a<=@intt)
begin
    select @result = @result + @a, @a = @a + 1
end
select @result
GO
EXEC TestFor 5

Kristen
Test

22859 Posts

Posted - 2006-11-21 : 13:45:05

SELECT SUM(MyNumber)
FROM MyTallyTable
WHERE MyNumber <= @intt

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 13:48:40

why complicate things?

select (@intt * @intt + @intt) / 2

Peter Larsson
Helsingborg, Sweden

EDIT: I knew several years studying mathematics on the university would pay off some time in the future. And that day is today

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 13:55:49

Kristen, I hope that tally table starts with either 0 or 1, and not a negative number

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-11-21 : 13:59:42

"I knew several years studying mathematics on the university would pay off some time in the future. And that day is today "

Pah! I learnt that as a kid:

X O O O O
X X O O O
X X X O O
X X X X O

So to total 1 - 4 its (4 x (4 + 1)) / 2

Kristen

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-21 : 14:05:30

Let's go recursive!

alter function SumNumbers(@i int)
returns int
as
begin
	declare @retVal int
	if @i = 0 set @retVal = 0
	else set @retVal = @i + dbo.SumNumbers(@i - 1)
	return @retVal
end
GO
select dbo.SumNumbers(5)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-21 : 14:44:56

SumNumbers() is limited by the SQL Nest level
This ...


    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function Testfor(ByVal number As Integer) As Integer
        If number <= 1 Then
            Return (1)
        Else
            Return number + Testfor(number - 1)
        End If
    End Function

... works pretty well, but causes a stack overflow somewhere under 50000

Peso's select (@intt * @intt + @intt) / 2 is the best.

Jay White

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-21 : 14:46:29

Yeah, I'm on a CLR kick lately ... I'm trying to build up that gut feel for when to use it ...

Jay White

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources