Using Fetch--Help
-
I am trying to understand cursor and fetch. I am new to programming so please bare with me. Below is a sample of what I am trying to do.
I have a score in a CASE. How do I go row by row to add a score up.
USE Goldmine_Common_db
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @unamefull varchar(50), @uactequiv varchar(20)
DECLARE uactequiv_cursor CURSOR FOR
SELECT unamefull, uactequiv
Begin
SELECT CASE
WHEN uactequiv BETWEEN 1 AND 19 THEN 25
WHEN uactequiv BETWEEN 20 AND 21 THEN 50
WHEN uactequiv BETWEEN 22 AND 23 THEN 75
When uactequiv BETWEEN 24 and 26 THEN 100
When uactequiv BETWEEN 27 and 30 THEN 125
WHEN uactequiv >=31 THEN 150
ELSE 0
END AS Score,
SUM( uactequiv) AS Total
FROM contact2
group by uactequiv
End
OPEN uactequiv_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM uactequiv_cursor
INTO @unamefull, @uactequiv
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
PRINT 'NAME: ' + @unamefull + ' ' Score,
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM uactequiv_cursor
INTO @unamefull, @uactequiv
END
CLOSE uactequiv_cursor
DEALLOCATE uactequiv_cursor
GO
-
Why a cursor?
If you can post post DDL, sample data and the required output, I'm sure there is a way without a cursor.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Do I just need to FETCH?
-
Shelley,
Frank is asking for a post of the table structures involved in the operation you're trying to perform... that way we might be able to find a non-cursor-based solution.
SJT
-
EXAMPLE:
Table Name: contact2
Columns:
accountno varchar(20) NOT NULL (000001234) (unique identifier)
unamefull varchar(50) NULL
uactequiv smallinit, null
userdef02 varchar20 null
userdef03 varchar20 null
If a person has a score in uactequiv that is between 1-19 they would receive a score worth 25 points.
If a person has a score in uactequiv that is between 20-21 they would receive a score worth 50 points.
If userdef02 or userdef03 has data in it, they would receive a score worth 75 points.
accountno 000001234 has a uactequiv that = 75 and userdef02 has data in it which is worth 50 then that persons total score is 125.
______________________________________
95121162206)HPV;TTay 31 10/18/04 - AUS NULL
output = accountno, uactequiv, userdef02, userdef03
-
Perfectly do-able without a cursor then:
Select
accountno,
unamefull,
CASE
WHEN uactequiv BETWEEN 1 AND 19 THEN 25 -- add points for uactequiv value
WHEN uactequiv BETWEEN 20 AND 21 THEN 50
WHEN uactequiv BETWEEN 22 AND 23 THEN 75
When uactequiv BETWEEN 24 and 26 THEN 100
When uactequiv BETWEEN 27 and 30 THEN 125
WHEN uactequiv >=31 THEN 150
ELSE 0
END +
CASE
WHEN userdef02 IS NOT NULL THEN 50 -- Add 50 points if userdef02 not null
ELSE 0
END +
CASE
WHEN userdef03 IS NOT NULL THEN 25 -- Add 25 points if userdef03 not null
ELSE 0
END As TOTALSCORE
FROM
YourTable
-
WOW-Thank you so much
-
What you've seen from PW is a SET-BASED solution to your problem. A real world comparison to a CURSOR-BASED solution (which you started off looking for) is the comparison of filling a cup with sugar using a spoon (SET-BASED) and filling a cup with sugar using a tweezers (CURSOR-BASED).
There is usually a large (or measurable) performance difference in favour of SET-BASED operations. CURSOR-BASED operations may be occasionally preferred IF you need to inspect data/ perform actions at a ROW-by-ROW level...where there is some interaction/relationship between one or more succeeding rows of an input dataset/resultset....ie where the sequence of rows influences some processing of later rows.
It would be advisable to go read some online ramblings about the PRO's/ CON's of the 2 methods and to understand what each can do for you.
-
What you've seen from PW is a SET-BASED solution to your problem. A real world comparison to a CURSOR-BASED solution (which you started off looking for) is the comparison of filling a cup with sugar using a spoon (SET-BASED) and filling a cup with sugar using a tweezers (CURSOR-BASED).
I must keep that phrase in mind next time such a discussion pops up. 
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I'll be generous and say it's not my original work....Robvolk or NR from SQLTeam.com provided that wisdom.
-
Anyway, glad you didn't say, it's a quote by Ron Soukup
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Good job, PW; you get one "WOW" point.
Spreading the gospel of set based processing.
cl
Signature is NULL
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply