Looping like vb - 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 2005 Forums

Transact-SQL (2005)

Looping like vb

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-02 : 22:29:41

Hi, i wanna know are sql server 05 allow us to do the following statement??

DECLARE @xxxx INT
SET @xxxx = 0
WHILE @xxxx<13
BEGIN
IF @yyyy(@xxxx) IS NULL
SET @yyyy(@xxxx) = 0
END

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-02 : 23:16:32

maybe i was not clear...

heres my example code with error
DECLARE @x INT
DECLARE @y1 INT
DECLARE @y2 INT
DECLARE @y3 INT
DECLARE @y4 INT
DECLARE @y5 INT
DECLARE @variable VARCHAR(30)

SET @x = 1
SET @y1 = 10
SET @y2 = 20
SET @y3 = 30
SET @y4 = 5
SET @y5 = 45

WHILE @x<6
BEGIN
if @y(@x) IS NOT NULL
print @y(@x)

SET @x = @x + 1
END
i would wan to have the result in
10
20
30
5
45

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-06-02 : 23:16:41

What's @yyyy?

This is OK, but I don't know what @yyyy is in your code.

DECLARE @xxxx INT
SET @xxxx = 0
WHILE @xxxx<13
BEGIN
PRINT @xxxx
SET @xxxx = @xxxx + 1
END

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-06-02 : 23:18:32

quote:
Originally posted by waterduck

maybe i was not clear...

heres my example code with error
DECLARE @x INT
DECLARE @y1 INT
DECLARE @y2 INT
DECLARE @y3 INT
DECLARE @y4 INT
DECLARE @y5 INT
DECLARE @variable VARCHAR(30)

SET @x = 1
SET @y1 = 10
SET @y2 = 20
SET @y3 = 30
SET @y4 = 5
SET @y5 = 45

WHILE @x<6
BEGIN
if @y(@x) IS NOT NULL
print @y(@x)

SET @x = @x + 1
END
i would wan to have the result in
10
20
30
5
45

I still don't know what you mean with @y(@x) - that's not valid in VB either

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-02 : 23:19:35

erm....is like a parameter in a parameter
@y(@x) will likely equal to @y1, @y1, @3 depending @x is wat digit

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-06-02 : 23:20:54

Actually I guess you really mean that @y should be an array?

If so, you'd need to use a temporary table or table variable but you need to explain what you actually want to do in the loop because in SQL code you typically don't need to loop, you rather run a query over the rows in the table.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-02 : 23:32:01

However i was doing FIFO(first in first out concept)
where i have my variable insert with value
@1=100
@2=100
@3=50
@4=51
@5=0
@6=0
@7=0
@8=0
@9=0
@10=0
@11=0
@12=0

so i need a loop to achieve the result of
@1=0
@2=99
@3=0
@4=0
@5=0
@6=0
@7=0
@8=0
@9=0
@10=0
@11=0
@12=0

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-06-02 : 23:37:04

OK, my mind reading is failing me, I cannot begin to guess how you get from
@1=100
@2=100
@3=50
@4=51
@5=0
@6=0
@7=0
@8=0
@9=0
@10=0
@11=0
@12=0

to

@1=0
@2=99
@3=0
@4=0
@5=0
@6=0
@7=0
@8=0
@9=0
@10=0
@11=0
@12=0

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-02 : 23:41:35

woops i write mistake!!!
However i was doing FIFO(first in first out concept)
where i have my variable insert with value
@1=100
@2=100
@3=-50
@4=-51
@5=0
@6=0
@7=0
@8=0
@9=0
@10=0
@11=0
@12=0

so i need a loop to achieve the result of
@1=0
@2=99
@3=0
@4=0
@5=0
@6=0
@7=0
@8=0
@9=0
@10=0
@11=0
@12=0
really really sorry...my mistake..i forgot to put minus...sorry sory T.T

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-06-02 : 23:45:33

Still can't guess what you're trying to do

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 23:53:58

if you have a table storing these values, just


select sum(value)
from   yourtable

will give you 99

What is the things that you need to do that can't handle by table & set-based query ?

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-02 : 23:55:10

hmmi think i have found another logic to do it...thx alot for accompany me^^

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 23:58:34

Good L[spoiler]D[/spoiler]uck

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-03 : 01:46:34

quote:
Originally posted by waterduck

hmmi think i have found another logic to do it...thx alot for accompany me^^

What is that logic?

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-03 : 06:16:07

Erm...guys i think i need help...
i have the concept in my mind, but i wasn't able to code it out

TABLEA
------
TYPE DATE(DD/MM/YY) QUANTITY
PDO 01/01/06 100
PDO 02/01/06 100
DO 01/01/07 50
DO 01/02/07 100
PDO 01/03/07 25
PDO 01/08/08 30

my concept goes like:
loop cursor tableA
---if @type = PDO
------if @bal >=@quantity
---------@bal = @bal - @quantity
------else
--------insert into #temp_table(type, date, quantity)
---if @type = DO
------set @bal = @bal + quantity
------loop #temp_table
---------if #temp_table.quantity>@bal
------------#temp_table.quantity=#temp_table.quantity-@bal
------------set @bal = 0
---------else
------------set @bal = @bal - #temp_table.quantity
------------#temp_table.quantity=0
------end loop
end loop

if @bal <> 0
---print 'balance is negatif'
else
---exec(select sum(quantity) from #temp_table)

sorry if i not able to describe detail...my english not so well

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-03 : 21:08:23

any1 can guide me?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-04 : 00:15:38

SOLVED ^^

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources