Cursor is returning all rows, i need only some how

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

Other SQL Server Topics (2005)

Cursor is returning all rows, i need only some how

Author

Topic

Pilot_Riaz
Starting Member

16 Posts

Posted - 2010-04-09 : 08:34:40

Hi,

I have written the following sql below which is a cursor which loops through the Gameplay table.
I want to print 'pass' where the gameid gameid = xxx and UseRLTData = 0 which is in my IF statement and has the value 0 for UseRLTData

My problem is the cursor is also printing the gameid xxx whch is not in my IF statement
I only want to print fail when the gameid = xxx and UseRLTData = 1

What do i need to do please?

My code:
use CVTGameplay
GO

DECLARE @GameID INT
DECLARE @GameName nvarchar(200)
DECLARE @UseRLTData INT
DECLARE @GameIDVar INT
DECLARE @GameNameVar nvarchar(200)
DECLARE @UseRLTDataVar INT

DECLARE db_cursor CURSOR FOR
SELECT GameID, GameName, UseRLTData
FROM CVTGameplay.dbo.Games

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @GameID, @GameName, @UseRLTData

WHILE @@FETCH_STATUS = 0
BEGIN
SET @GameIDVar = @GameID
SET @GameNameVar = @GameName
SET @UseRLTDataVar = @UseRLTData

-- During the cursor loop in the table, if the game id below is returned (It is the game i want to check the UseRLTData value)

IF @GameIDVar = 40005 or @GameIDVar = 40001 or @GameIDVar = 40017 or @GameIDVar = 40019 or @GameIDVar = 40038
or @GameIDVar = 40028 or @GameIDVar = 40018 or @GameIDVar= 40002 or @GameIDVar = 40020 or @GameIDVar = 40004
or @GameIDVar = 40016 or @GameIDVar = 40037 or @GameIDVar = 40015 or @GameIDVar = 40035 or @GameIDVar = 40041 or
@GameIDVar = 40010 or @GameIDVar = 40008 or @GameIDVar = 40014 or @GameIDVar = 40009 or @GameIDVar = 40007
or @GameIDVar = 40006 or @GameIDVar = 40011 or @GameIDVar = 40013 or @GameIDVar = 40003 and
@UseRLTDataVar = 0

BEGIN
-- the gameID has the UseRLTData value 0 so it is a pass
print '@GameID = ' + CAST( @GameIDVar as char(5) ) + ' pass'
print 'pass'

FETCH NEXT FROM db_cursor INTO @GameID, @GameName, @UseRLTData
END
ELSE
BEGIN
-- the gameID has the UseRLTData value 1 so it is a fail for my test
print '@GameID = ' + CAST( @GameIDVar as char(5) ) + ' fail'

FETCH NEXT FROM db_cursor INTO @GameID, @GameName, @UseRLTData
END
END
CLOSE db_cursor
DEALLOCATE db_cursor

Output from console looks like this
@GameID = 0 fail
@GameID = 1 fail
@GameID = 2 fail
@GameID = 3 fail
@GameID = 4 fail
@GameID = 5 fail
@GameID = 6 fail
@GameID = 7 fail
@GameID = 8 fail
@GameID = 9 fail
@GameID = 10 fail
@GameID = 11 fail
@GameID = 12 fail
@GameID = 13 fail
@GameID = 14 fail
etc

Riaz

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-09 : 08:51:47

Change your IF condition to:

IF (@GameIDVar = 40005 or @GameIDVar = 40001 or @GameIDVar = 40017 or @GameIDVar = 40019 or @GameIDVar = 40038
or @GameIDVar = 40028 or @GameIDVar = 40018 or @GameIDVar= 40002 or @GameIDVar = 40020 or @GameIDVar = 40004
or @GameIDVar = 40016 or @GameIDVar = 40037 or @GameIDVar = 40015 or @GameIDVar = 40035 or @GameIDVar = 40041 or
@GameIDVar = 40010 or @GameIDVar = 40008 or @GameIDVar = 40014 or @GameIDVar = 40009 or @GameIDVar = 40007
or @GameIDVar = 40006 or @GameIDVar = 40011 or @GameIDVar = 40013 or @GameIDVar = 40003) and
@UseRLTDataVar = 0

Harsh Athalye
http://www.letsgeek.net/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-09 : 08:54:23

As a side note you can minimize so many OR by using IN operator like below:


IF @GameIDVar in (40005, 40001, 40017, 40019,...)

Harsh Athalye
http://www.letsgeek.net/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-09 : 09:04:06

See if you get the required result by using only this code

select gameid,case when gameid IN (40005,....) and UseRLTData = 0 then 'pass' else 'fail' end
from your_table

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 09:19:30

Rather than use a cursor to print the outputs, why not just use a select statement to select them?


SELECT GameID, CASE WHEN GameID IN (40005, 40001, 40017, 40019, 40038,
		40028, 40018, 40002, 40020, 40004,
		40016, 40037, 40015, 40035, 40041, 
		40010, 40008, 40014, 40009, 40007,
		40006, 40011, 40013, 40003) 
		AND UseRLTData = 0 THEN 'Pass' 
	ELSE 'Fail' END AS Result
FROM CVTGameplay.dbo.Games

Also, it's not really a good idea to use 3 part table names to reference tables in the current database. The reason for that is, suppose you want to make a copy of the database, but under a different name. Any queries the use 3 part table names will still reference the original database, not the copy.

There are 10 types of people in the world, those that understand binary, and those that don't.

Pilot_Riaz
Starting Member

16 Posts

Posted - 2010-04-09 : 12:23:28

Thanks for the replies,
I have tried this one

SELECT GameID, CASE WHEN GameID IN (40005, 40001, 40017, 40019, 40038,
40028, 40018, 40002, 40020, 40004,
40016, 40037, 40015, 40035, 40041,
40010, 40008, 40014, 40009, 40007,
40006, 40011, 40013, 40003)
AND UseRLTData = 0 THEN 'Pass'
ELSE 'Fail' END AS Result
FROM CVTGameplay.dbo.Games

But it returns fail for everysingle gameid
I would like fail only to return when the above mentioned gameIDs and UseRLTData has the value 0

Riaz

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 12:50:19

Can you please post the result of this select statement:


SELECT GameID, UseRLTData
FROM CVTGameplay.dbo.Games
WHERE GameID IN (40005, 40001, 40017, 40019, 40038,
40028, 40018, 40002, 40020, 40004,
40016, 40037, 40015, 40035, 40041,
40010, 40008, 40014, 40009, 40007,
40006, 40011, 40013, 40003)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Pilot_Riaz
Starting Member

16 Posts

Posted - 2010-04-12 : 05:52:48

The results are:
0 Fail
1 Fail
2 Fail
3 Fail
..
717 Fail
718 Fail
..
40034 Fail
40035 Pass
40036 Fail
40037 Pass
40038 Pass

Now the game ids i am only interested in are:
40005, 40001, 40017, 40019, 40038, 40028, 40018, 40002, 40020, 40004,
40016, 40037, 40015, 40035, 40041, 40010, 40008, 40014, 40009, 40007,
40006, 40011, 40013, 40003

The query is returning all game ids.
When a game ID e.g. 40037 has the value 0 for UseRLTData columnn then it should print 'Fail' otherwise 'Pass' if the value is 1

Riaz

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-12 : 05:58:19

How about this:

SELECT GameID, CASE WHEN UseRLTData = 0 THEN 'Pass'
ELSE 'Fail' END AS Result
FROM CVTGameplay.dbo.Games
WHERE GameID IN (40005, 40001, 40017, 40019, 40038,
40028, 40018, 40002, 40020, 40004,
40016, 40037, 40015, 40035, 40041,
40010, 40008, 40014, 40009, 40007,
40006, 40011, 40013, 40003)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Pilot_Riaz
Starting Member

16 Posts

Posted - 2010-04-12 : 06:52:28

Awesome works great. I get the results i was expecting now.

e.g.
40001 Fail
40002 Pass
40003 Pass
40004 Pass

I didn't need to use Cursor and all that code from my 1st post.
Thanks very much, much appreciated for everyone's help ;)

Riaz

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-12 : 07:07:22

quote:
Originally posted by Pilot_Riaz

I didn't need to use Cursor and all that code from my 1st post.
Thanks very much, much appreciated for everyone's help ;)

Riaz

You hardly ever do need to use the CURSOR and if you are then there's almost certainly a better way.

Best of luck,

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

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-12 : 07:50:49

quote:
Originally posted by Transact Charlie
You hardly ever do need to use the CURSOR and if you are then there's almost certainly a better way.

Just to give you an idea of why it's such a bad idea to use cursors. I was once asked to write a fairly complex script to update some data for a client. They gave me a table that contained the values that needed updating, and I wrote a set based script containing a series of update statement, which took 5 minute to run in our tesd environment. As a matter of courtesy, I ran the script past my boss before applying it to production. He pointed out that I had not accounted for a possibility that, although was not relevant to the current update, may arise in the future if this procedure needed to be applied again. I explained that I had already gone over that with the client, and they had told me there was no chance of this possibility ever occuring, EVER. But the boss wanted to allow for it anyway.

To allow for this possibility, there was only one option available to me, use cursors and update each record individually. The script with cursors took 14 hour to run, and produced the same results as the original. I passed it around the office, asking all if they could see any way to improve it, but nobody could (And these were pretty smart people).

5 minutes Vs 14 hours. Speaks for itself.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-12 : 09:11:26

quote:
Originally posted by Pilot_Riaz

Awesome works great. I get the results i was expecting now.

e.g.
40001 Fail
40002 Pass
40003 Pass
40004 Pass

I didn't need to use Cursor and all that code from my 1st post.
Thanks very much, much appreciated for everyone's help ;)

Riaz

This is why when start asking questions tell us "what yow want" than "how to do this way"

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-12 : 10:15:45

quote:
Originally posted by madhivanan
This is why when start asking questions tell us "what yow want" than "how to do this way"

That's pretty much what Pilot_Riaz did. He told us what he wanted, and showed what he had so far. When you read his opening post, it's clear that he's asking for the precise result he ended up with. It's just that many (myself included) misread the question. He had a crack himself, and was unsuccessful. So he posted everything he'd done so far, and asked why it wasn't working and how to fix it. There's nothing wrong with that.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources