How to use While - 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)

How to use While

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2010-05-11 : 03:00:34

Hi,I'm new in Sql Programming. Actually I'm a VB programmer. I know the concept of Do While Loop but don't know how to use it with procedures. Actually I've to fire a query and calculate something from top to bottom
I tried this using cursor but it is taking much time.
here is my Code

CREATE PROCEDURE [dbo].[MakePairs]
(@SpCode int)
AS
BEGIN
DECLARE
@AppCode int,
@SponsorID int,
@NewAppCode int,
@NewSponsorID int,
@NormalStatus int,
@Side int

DECLARE SRst_Cursor CURSOR FOR
select AppCode,SpCode,NormalStatus,Side from Trans Where SpCode =@SpCode;
OPEN SRst_Cursor
FETCH NEXT FROM SRst_Cursor into @AppCode,@SponsorID,@NormalStatus,@Side
WHILE @@FETCH_STATUS =0
BEGIN
if @NormalStatus =1
BEGIN
Select TOP 1 @NewAppCode=AppCode,@NewSponsorID=SpCode from trans where AppCode=@AppCode and NormalStatus = 1 and side = case When @Side=1 then 0 else 1 end and oCode is null;
if @@rowcount > 0
begin

update trans set ocode=@SpCode Where AppCode=@NewAppCode and SpCode=@NewSponsorID and NormalStatus =1;
update trans set oCode=@NewSponsorID Where AppCode=@AppCode and SpCode=@SponsorID and NormalStatus =@NormalStatus and Side =@Side;
end
END

FETCH NEXT FROM SRst_Cursor into @AppCode,@SponsorID,@NormalStatus,@Side
END
CLOSE SRst_Cursor
DEALLOCATE SRst_Cursor
Set nocount off
END

How could I do the same with WHILE loop. Should it increase performance. Above query is taking at least 10-12 hours to execure with 9 lack records. What could be the best solution.

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-11 : 03:34:58

Same you can do with while loop which will be definitely faster than cursor
Try this -


CREATE PROCEDURE [dbo].[MakePairs]
(
	@SpCode int
)
AS
BEGIN
SET NOCOUNT ON
	DECLARE
	@AppCode int,
	@SponsorID int,
	@NewAppCode int,
	@NewSponsorID int,
	@NormalStatus int,
	@Side int

	DECLARE @RowNo int
	SET @RowNo = 1

	DECLARE @SRst AS TABLE ( RowNo int , AppCode int, SpCode int, NormalStatus int,Side int)

	INSERT INTO @SRst 
	SELECT ROW_NUMBER() OVER( Partition BY 1 ORDER BY (SELECT 1) ) RowNo, AppCode, SpCode, NormalStatus, Side from Trans Where SpCode =@SpCode

	WHILE @RowNo < = ( SELECT MAX(RowNo) FROM @SRst )
	BEGIN
		SELECT	@AppCode = AppCode, @SponsorID = SpCode, @NormalStatus = NormalStatus, @Side = Side 
		FROM	@SRst 
		WHERE	RowNo = @RowNo

		IF @NormalStatus =1
		BEGIN
			SELECT TOP 1 @NewAppCode=AppCode,@NewSponsorID=SpCode FROM trans WHERE AppCode=@AppCode and NormalStatus = 1 and side = case When @Side=1 then 0 else 1 end and oCode is null;
			IF @@ROWCOUNT > 0
			BEGIN
				UPDATE trans SET ocode=@SpCode WHERE AppCode=@NewAppCode and SpCode=@NewSponsorID and NormalStatus =1;
				UPDATE trans SET oCode=@NewSponsorID WHERE AppCode=@AppCode and SpCode=@SponsorID and NormalStatus =@NormalStatus and Side =@Side;
			END
		END
		@RowNo = @RowNo + 1	
	END
	SET NOCOUNT OFF
END

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

ravininave
Posting Yak Master

111 Posts

Posted - 2010-05-11 : 03:54:09

am trying, thanx 4 quick reply. I think @RowNo = @RowNo + 1 should be Set @RowNo = @RowNo + 1

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-11 : 04:34:36

welcome

thats the typo error...
I just replaced the things i dint execute it.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

ravininave
Posting Yak Master

111 Posts

Posted - 2010-05-11 : 04:58:05

Still it's taking much time to execute. Is there any other way.?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-11 : 05:10:38

quote:
Originally posted by vaibhavktiwari83

Same you can do with while loop which will be definitely faster than cursor
Try this -

why do you think this? A loop is a loop is a loop. Still slow.

ravininave -- state what you WANT to accomplish and the data sets you have and the desired output. There will (almost certainly) be a way to do this without using any kind of loop.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-11 : 05:19:23

Here is your code with a little formatting:


CREATE PROCEDURE [dbo].[MakePairs] (
	@SpCode int
	)
AS BEGIN

DECLARE
	@AppCode int
	, @SponsorID int
	, @NewAppCode int
	, @NewSponsorID int
	, @NormalStatus int
	, @Side int

DECLARE SRst_Cursor CURSOR FOR select
	AppCode
	, SpCode
	, NormalStatus
	, Side
from
	Trans
Where
	SpCode = @SpCode;

OPEN SRst_Cursor

	FETCH NEXT FROM SRst_Cursor into
		@AppCode
		, @SponsorID
		, @NormalStatus
		, @Side

	WHILE @@FETCH_STATUS = 0  BEGIN

		if @NormalStatus = 1 BEGIN

			Select TOP 1
				@NewAppCode = AppCode
				, @NewSponsorID = SpCode
			from
				trans
			where
				AppCode = @AppCode
				and NormalStatus = 1
				and side = case When @Side=1 then 0 else 1 end and oCode is null

			if @@rowcount > 0 begin
				update trans set ocode = @SpCode
				Where
					AppCode = @NewAppCode
					and SpCode = @NewSponsorID
					and NormalStatus = 1;	

				update trans set oCode = @NewSponsorID
				Where
					AppCode = @AppCode
					and SpCode = @SponsorID
					and NormalStatus = @NormalStatus
					and Side = @Side;
			end
		END

		FETCH NEXT FROM SRst_Cursor into @AppCode,@SponsorID,@NormalStatus,@Side

	END
CLOSE SRst_Cursor 
DEALLOCATE SRst_Cursor

Set nocount off 
END

There are a few things I can see straight away.

1st) You get all rows back from the trans table where SpCode = @SpCode in your CURSOR but you only do something if NormalStatus = 1 so that should be part of the where clause.

2nd) This portion


Select TOP 1
				@NewAppCode = AppCode
				, @NewSponsorID = SpCode
			from
				trans
			where
				AppCode = @AppCode
				and NormalStatus = 1
				and side = case When @Side=1 then 0 else 1 end and oCode is null

Doesn't have an order by so the TOP doesn't guarantee a consistent row on repeated runs. What is this supposed to do?

I have a gut feeling that this entire proc can be writeen as either 1 or 2 simple UPDATE statements without all the cursor nonsense.

IF you state what your date is like currently and what you want to do based on that sample data then we'll help you produce a nice simple set based solution.

Regards,
Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ravininave
Posting Yak Master

111 Posts

Posted - 2010-05-11 : 05:52:38

Well, this is the MLM Package and it is matching A-B Pairs.
I've a Data Like this
Table Structure

SpCode AppCode DOJ SLAB NormalStatus SIDE OCode
1 2 01/01/2010 1 1 0
2 3 01/01/2010 1 0 1
1 3 01/01/2010 1 0 1
2 4 01/01/2010 2 1 0 5
1 4 01/01/2010 2 1 0
2 5 01/01/2010 2 1 1 4
1 5 01/01/2010 1 1 0 1
3 6 01/01/2010 1 1 0
1 6 01/01/2010 1 1 1 1
3 7 01/01/2010 2 0 1
1 7 01/01/2010 2 0 1
4 8 01/01/2010 2 1 0
2 8 01/01/2010 1 1 0
1 8 01/01/2010 1 1 0

If side is 0 then opponent matching ID would be 1 with same sponsorID. But BOTH MATCHING IDS should be Paid Id meansNormalStauts =1.
In above case matching IDs are

2= 5 & 4
1=5 & 6
1=7 & 8

‘------------------------
In my Procedure suppose I pass parameter as 4
Then First I’ve to fetch data with SponsorID = 4
select AppCode,SpCode,NormalStatus,Side from Trans Where SpCode =@SpCode;
I would get 2 rows as follows :
AppCode SPCode normalStatus side
2 4 1 0
1 4 1 0

Now I’ve to calculate Opposite Sides.
So I would find data Where AppCode = 2 and NormalStatus =1 and Side = 1
In first case of 2 I found matching with 2 and id = 5 and side =1

So, I've to achieve this. Can anyone simplify this??

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-11 : 06:39:52

select AppCode,SpCode,NormalStatus,Side from Trans Where SpCode =@SpCode; where you get 2 rows returned doesn't match the data you posted. Did you transpose the columns SpCoda and AppCode?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ravininave
Posting Yak Master

111 Posts

Posted - 2010-05-11 : 06:46:20

But I've to calculate for each rows

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-11 : 07:06:48

Sigh.

This is the data you said you had:


SpCode	AppCode	DOJ	SLAB	NormalStatus	SIDE	OCode
1	2	01/01/2010	1	1	0	
2	3	01/01/2010	1	0	1	
1	3	01/01/2010	1	0	1	
2	4	01/01/2010	2	1	0	5
1	4	01/01/2010	2	1	0	
2	5	01/01/2010	2	1	1	4
1	5	01/01/2010	1	1	0	1
3	6	01/01/2010	1	1	0	
1	6	01/01/2010	1	1	1	1
3	7	01/01/2010	2	0	1	
1	7	01/01/2010	2	0	1	
4	8	01/01/2010	2	1	0	
2	8	01/01/2010	1	1	0	
1	8	01/01/2010	1	1	0

And this is the query you said:(@spCode = 4)


select AppCode,SpCode,NormalStatus,Side from Trans Where SpCode =@SpCode;

How does that return what you said:


AppCode	SPCode	normalStatus	 side
2	 4	 1	 0
1	 4	 1	 0

When I can only see one row matching row for SpCode in your data not 2 Is your data set wrong (do you have the SpCode and AppCode columns round the wrong way????)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ravininave
Posting Yak Master

111 Posts

Posted - 2010-05-11 : 09:26:31

Sorry, in above table I've to write AppCode instead of SpCode and SpCode instead of AppCode.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-11 : 11:39:37

OK. I think I'm beginning to understand.

I'm still not sure what this means:

quote:

Now I’ve to calculate Opposite Sides.
So I would find data Where AppCode = 2 and NormalStatus =1 and Side = 1
In first case of 2 I found matching with 2 and id = 5 and side =1

So, I've to achieve this. Can anyone simplify this??

There's no ID column so is this a field you missed out? I've had to guess at a key for your table also -- does the table even have a primary key?

Anyway Does this code produce the results you would expect? (does it produce the right MATCH columns) It's safe to just run this standalone


/*Well, this is the MLM Package and it is matching A-B Pairs.
I've a Data Like this
Table Structure

SpCode	AppCode	DOJ	SLAB	NormalStatus	SIDE	OCode
1	2	01/01/2010	1	1	0	
2	3	01/01/2010	1	0	1	
1	3	01/01/2010	1	0	1	
2	4	01/01/2010	2	1	0	5
1	4	01/01/2010	2	1	0	
2	5	01/01/2010	2	1	1	4
1	5	01/01/2010	1	1	0	1
3	6	01/01/2010	1	1	0	
1	6	01/01/2010	1	1	1	1
3	7	01/01/2010	2	0	1	
1	7	01/01/2010	2	0	1	
4	8	01/01/2010	2	1	0	
2	8	01/01/2010	1	1	0	
1	8	01/01/2010	1	1	0	
*/
DECLARE @trans TABLE (
	[AppCode] INT
	, [SpCode] INT
	, [DOJ] DATETIME
	, [SLAB] INT
	, [NORMALSTATUS] BIT
	, [SIDE] BIT
	, [OCODE] INT

	PRIMARY KEY ([appCode], [spCode])
	)

INSERT @trans
          SELECT 1,	2,	'01/01/2010',	1,	1,	0,	NULL
UNION ALL SELECT 2,	3,	'01/01/2010',	1,	0,	1,	NULL
UNION ALL SELECT 1,	3,	'01/01/2010',	1,	0,	1,	NULL
UNION ALL SELECT 2,	4,	'01/01/2010',	2,	1,	0,	5
UNION ALL SELECT 1,	4,	'01/01/2010',	2,	1,	0,	NULL
UNION ALL SELECT 2,	5,	'01/01/2010',	2,	1,	1,	4
UNION ALL SELECT 1,	5,	'01/01/2010',	1,	1,	0,	1
UNION ALL SELECT 3,	6,	'01/01/2010',	1,	1,	0,	NULL
UNION ALL SELECT 1,	6,	'01/01/2010',	1,	1,	1,	1
UNION ALL SELECT 3,	7,	'01/01/2010',	2,	0,	1,	NULL
UNION ALL SELECT 1,	7,	'01/01/2010',	2,	0,	1,	NULL
UNION ALL SELECT 4,	8,	'01/01/2010',	2,	1,	0,	NULL
UNION ALL SELECT 2,	8,	'01/01/2010',	1,	1,	0,	NULL
UNION ALL SELECT 1,	8,	'01/01/2010',	1,	1,	0,	NULL

/*
If side is 0 then opponent matching ID would be 1 with same sponsorID. But BOTH MATCHING IDS should be Paid Id meansNormalStauts =1.
In above case matching IDs are

2= 5 & 4
1=5 & 6
1=7 & 8

‘------------------------
In my Procedure suppose I pass parameter as 4
Then First I’ve to fetch data with SponsorID = 4
select AppCode,SpCode,NormalStatus,Side from Trans Where SpCode =@SpCode;
I would get 2 rows as follows :
AppCode	SPCode	normalStatus	side
2		4		1				0
1		4		1				0
*/
DECLARE @spCode INT
SET @spCode = 4
SELECT [AppCode], [SpCode], [NormalStatus], [Side] FROM @trans WHERE [spCode] = @spCode
/*
Now I’ve to calculate Opposite Sides.
So I would find data Where AppCode = 2 and NormalStatus =1 and Side = 1 
In first case of 2 I found matching with 2 and id = 5 and side =1

So, I've to achieve this. Can anyone simplify this??
*/

SELECT
	t.[appCode]
	, t.[spCode]
	, t.[NormalStatus]
	, t.[Side]
	, m.[Matching appCode]
	, m.[matching spCode]
FROM
	@trans t
	OUTER APPLY (
		SELECT
			[appCode] AS [Matching appCode]
			, [spCode] AS [Matching spCode]
		FROM
			@trans m
		WHERE
			m.[appCode] = t.[appCode]
			AND m.[normalStatus] = t.[normalStatus]
			AND m.[side] <> t.[side]
		)
		m
WHERE
	t.[spCode] = @spCode

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources