1000% will give all of you all scold

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)

1000% will give all of you all scold

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-06 : 02:32:08

1000% will give all of you all scold, my topic will be...how to break from cursor?

WHILE @@FETCH_STATUS = 0
BEGIN
	if do something
		BREAK?
FETCH NEXT FROM STK_CURSOR
INTO @me
END

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-06 : 02:32:34

peso sure dump me into pasific sea

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-06 : 02:35:42

found =.=""" http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109116

Hope can help...but advise to wait pros with confirmation...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 02:43:02

[code]declare @UserBreak BIT

SET @UserBreak = 0

WHILE @@FETCH_STATUS = 0 AND @userBreak = 0
BEGIN
if do something
begin
BREAK?
set @userbreak = 1
end
FETCH NEXT FROM STK_CURSOR
INTO @me
END[/code]

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-06 : 03:03:20

quote:
Originally posted by waterduck

1000% will give all of you all scold, my topic will be...how to break from cursor?

WHILE @@FETCH_STATUS = 0
BEGIN
if do something
BREAK?
FETCH NEXT FROM STK_CURSOR
INTO @me
END

Hope can help...but advise to wait pros with confirmation...

What are you doing with the cursor?

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-06 : 03:06:52

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130734
this...couldn't think out a set based approach...

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-06 : 03:34:27

erm...i solve the problem already ^^

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-06 : 03:37:56

Did you delete the link?

Is this?

DECLARE @tempfun TABLE
(
col1 VARCHAR(10),
col2 int,
col3 int,
col4 DATETIME,
typi CHAR(3)
)

INSERT INTO @tempfun
SELECT 'NOMOVE',1,1, '20070101','PDO' UNION ALL
SELECT 'NOMOVE',2,2, '20070102','PDO' UNION ALL
SELECT 'NOMOVE',3,3, '20070103','PDO' UNION ALL
SELECT 'NOMOVE',4,4, '20070104','PDO' UNION ALL
SELECT 'NOMOVE',5,5, '20070105','PDO' UNION ALL
SELECT 'NOMOVE',6,6, '20070106','PDO' UNION ALL
SELECT 'NOMOVE',7,7, '20070107','PDO' UNION ALL
SELECT 'NOMOVE',8,8, '20070110','PDO' UNION ALL
SELECT 'MOVE',9,9, '20070109','PDO' UNION ALL
SELECT 'MOVE',10,10, '20070110','INV'

DECLARE @enddate DATETIME SET @enddate = '20070111'
DECLARE @nomove INT SET @nomove = 1
SELECT col1, sum(col2), sum(col3) FROM @tempfun
WHERE col1 in
(
SELECT col1
FROM @tempfun
where col4 >= DATEADD(DAY, 0-@nomove, @enddate) and typi='INV'
)
GROUP BY col1

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-06 : 03:43:31

yup i deleted....
anyway thx madhi^^

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-06 : 03:46:42

You can have my full query

SELECT	COALESCE(t.total_price, 0), COALESCE(t.quantity, 0), COALESCE(t.qtt_out, 0), t.do_date, t.trx_type, CASE WHEN h.forex_rate IS NULL OR h.forex_rate = 0 then 1 ELSE h.forex_rate END forex_rate
FROM	(
	SELECT	CASE WHEN trx_type = 'ADJ' and quantity > 0 and (SELECT content a FROM sys_config WHERE header = 'ST_ADJASCOST')='Y' then 'PDO'
		WHEN trx_type = 'WHX' and quantity > 0 and (SELECT content a FROM sys_config WHERE header = 'ST_WHXASCOST')='Y' then 'PDO' else trx_type END trx_type,
		trnx_ref, quantity, total_price, qtt_out, do_date
	FROM	(SELECT	trnx_ref, trx_type, st_code, quantity, qtt_out, total_price, do_date
		FROM	st_trx
		WHERE	((@wh = '' or @wh IS NULL) or uom2 = @wh) and
			(do_date <= @enddate) and
			st_code NOT IN (SELECT	st_code
					FROM	st_trx
					WHERE	(do_date >= DATEADD(DAY, @nomove, @enddate) and do_date <= @enddate and trx_type <> 'PDO'))
	)t right join(
	SELECT	st_code
	FROM	st_mast
	WHERE	(st_code=@st_code)and (@ds = 'a' or @ds = 'i')
	UNION
	SELECT	st_code
	FROM	st_msuspend
	WHERE	(st_code=@st_code)and (@ds = 'o' or @ds = 'i')
	)m on t.st_code=m.st_code
	)t left join st_head h on t.trnx_ref=h.do_no and t.trx_type=h.trx_type
ORDER BY t.do_date

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-06 : 03:49:57

Ok. you are entering to set-based world?

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-06 : 03:57:25

still hardworking to it, but still using cursor based approach to print out every row; now what i can do is ONLY use 1 cursor to make everything out instead of few cursor.. althought no point but no choice

Hope can help...but advise to wait pros with confirmation...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 04:01:58

Why do you like to PRINT every data?
You know you fill the log?

Even if you are using a CURSOR, my experience is that the code is 30% faster without printing than with printing.

N 56°04'39.26"
E 12°55'05.63"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-06 : 04:05:30

quote:
Why do you like to PRINT every data?

Not i like, I DON'T LIKE IT at all...but no choice, my internship in this company conduct this method for ages, i can't do anything to it...all i can do for now is try my best to use set-based query to minimize the usage of cursor based, so next trainee would have a better life compare to me

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-06 : 04:06:33

plus, by just remove away the cursor thingy, my query can work perfectly i think

edit.wrong emo

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources