asked on

Adding a given start number and end number in T-SQL without using loop

Hi, I want to create a stored procedure where you pass a start number and an end number. For example 1 as your start number and 1000 as your end number, then the output should be the sum of 1-1000.

We can do it easily using loops. But my requirement is to do it without using loops.

Can any one please give me that logic ?

Many Thanks

Jim Horn

>But my requirement is to do it without using loops.
Why's that? Homework?

PortletPaul

sum of 1-1000 = 999

I think you mean 1 + 2 + 3 + 4 ... + 1000
(the sum of all numbers from 1 to 1000)

is that correct?

gvamsimba

ASKER

Hi Portlet , yes that is correct.

gvamsimba

ASKER

I think you mean 1 + 2 + 3 + 4 ... + 1000
(the sum of all numbers from 1 to 1000)

PortletPaul

and Jim's questions?

gvamsimba

ASKER

yes kind of Homework :-)

PortletPaul

thanks for the honesty.

we can only guide you or help if your current approach has hit a snag

what have you tried so far?

PortletPaul

tip:
does the name Karl Friedrich Gauss
mean anything to you?

gvamsimba

ASKER

yes he is a German mathematician, why ?

gvamsimba

ASKER

Hi Portlet, I am not a sql guru yet and hence I posted the question on this website...Yes I have done this but using loops...But now the requirement is without using loops.

can you suggest me of any idea ?

PortletPaul

Gauss worked out a method of quickly summing all numbers from 1 to 100

several maths sites explain the technique

it requires working out a repeating pattern, and I believe it would apply here (with some effort)

gvamsimba

ASKER

so can I not got any help from this site then ? I do not have enough time for research and hence I have posted it here.

Philip Portnoy

Hi there,

here's the code for you for calculating 1 to N:

SET @sum = (1 + @lastnumber) * (@lastnubmer / 2)

But we need to calculate M to N, which is basically (1 to N) - (1 to M). So it will be:

SET @sum = ((1 + @lastnumber) * (@lastnumber / 2)) - ((1 + @firstnumber) * (@firstnumber / 2))

gvamsimba

ASKER

Hi Jim,Ok, I was not aware of this policy, but thanks for letting me know and I will make sure I will follow these guidelines from now on.

ASKER CERTIFIED SOLUTION

gvamsimba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

Thanks. In that case, give this a whirl..

Declare @firstnumber int = 0, @lastnumber int = 6
Declare @runningsum int = 0, @counter int = @firstnumber

WHILE @counter <= @lastnumber
      begin
      SET @runningsum = @runningsum + @counter
      SET @counter = @counter + 1
      end

SELECT @runningsum

Open in new window

gvamsimba

ASKER

Hi Jim,

Thanks for this, but As you can see from my above posts yesterday, I have already done this using loops but the requirement was to achieve this without using loops...

Cheers

Jim Horn

Again, spell out the requirement. Ten bucks says you didn't get it from a business user, so either this is coming from a homework assigment (which means it's against policy for us to answer it) or a DBA got a wild hair and is torturing you for fun.

Spill.

gvamsimba

ASKER

Hi Jim,

Yes you are right, this is not exactly homework...our DBA has challenged me to do this without using loops .
However he was happy with my solution which I have described above and it works in all scenarios. I did not use a loop , however I have just used recursive CTE :-)

gvamsimba

ASKER

good