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.
| 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. |
|
|
|
|
|
|
|
|
Cursor is returning all rows, i need only some how