How to check that the Cursor is on the last row?
in [Server Configuration]
|
From: Cindy Lee on 18 Jun 2010 17:46 Is there an easy way to check that the cursor you're fetching through is on it's last row? I can get the rowcount, but I don't want to select count(*) from the table.
From: Erland Sommarskog on 19 Jun 2010 06:26 Cindy Lee (danparker276(a)yahoo.com) writes: > Is there an easy way to check that the cursor you're fetching through > is on it's last row? > > I can get the rowcount, but I don't want to select count(*) from the > table. After the FETCH statement, check @@fetch_status. If <> 0 you are at the end. You can also use @@cursor_rows to see how many rows the cursor has produced. Note that this does not work with dynamic cursors, the default cursor type. But you should always declare your cursors DECLARE cur CURSOR STATIC LOCAL unless you have a special reason not to. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Cindy Lee on 19 Jun 2010 16:14 On Jun 19, 3:26 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > Cindy Lee (danparker...(a)yahoo.com) writes: > > Is there an easy way to check that the cursor you're fetching through > > is on it's last row? > > > I can get the rowcount, but I don't want to select count(*) from the > > table. > > After the FETCH statement, check @@fetch_status. If <> 0 you are at the > end. > > You can also use @@cursor_rows to see how many rows the cursor has > produced. Note that this does not work with dynamic cursors, the > default cursor type. But you should always declare your cursors > > DECLARE cur CURSOR STATIC LOCAL > > unless you have a special reason not to. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Yeah, I'm using forward only cursors. Problem is I need to know if it's the last row before i fetch. I'm splitting money by percentages and I round up on the first few rows, and the the last row gets what every money is left over. Otherwise there will be an extra penny. I can either check if the amount left is less than the amount by percentage, or check to see if it's the last row and then just take the amount left. I'm subtracting from the total amount everytime someone gets a percentage. I think it would be easier to just check if it's the last row rather than if the amount left is less than the percentage amount. So there is no way to tell what the total count will be?
From: Erland Sommarskog on 19 Jun 2010 16:39 Cindy Lee (danparker276(a)yahoo.com) writes: > Yeah, I'm using forward only cursors. Problem is I need to know if > it's the last row before i fetch. I'm splitting money by percentages > and I round up on the first few rows, and the the last row gets what > every money is left over. Otherwise there will be an extra penny. > I can either check if the amount left is less than the amount by > percentage, or check to see if it's the last row and then just take > the amount left. > I'm subtracting from the total amount everytime someone gets a > percentage. > I think it would be easier to just check if it's the last row rather > than if the amount left is less than the percentage amount. > > So there is no way to tell what the total count will be? You could save @@cursor_rows into a variable, and then decrement it, but better is to add this column to your query: row_number() OVER(ORDER BY ... ) The columns where are the same as you order on in your ORDER BY clause, but the order should be the reverse. That is, if the cursor has this ORDER BY clause: ORDER BY col1, col2 DESC, col3 Your OVER clause needs to be: row_number() OVER(col1 DESC, col2 ASC, col3 DESC) When you get 1 in the varible that you populate from this column, you are on the last row in the cursor. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Jeroen Mostert on 20 Jun 2010 04:46 On 2010-06-19 22:14, Cindy Lee wrote: > Yeah, I'm using forward only cursors. Problem is I need to know if > it's the last row before i fetch. I'm splitting money by percentages > and I round up on the first few rows, and the the last row gets what > every money is left over. Otherwise there will be an extra penny. > I can either check if the amount left is less than the amount by > percentage, or check to see if it's the last row and then just take > the amount left. > I'm subtracting from the total amount everytime someone gets a > percentage. > I think it would be easier to just check if it's the last row rather > than if the amount left is less than the percentage amount. > You can always complicate the logic by processing row N after fetching row N + 1. The last row is then processed after all the others. FETCH NEXT FROM c INTO @a1, @b1, @c1, @d1, @e1; WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM c INTO @a2, @b2, @c2, @d2, @e2; IF @@FETCH_STATUS <> 0 BREAK; -- Process a1, b1, c1, d1, e1 SELECT @a1 = @a2, @b1 = @b2, @c1 = @c2, @d1 = @d2, @e1 = @e2; END -- Process a1, b1, c1, d1, e1 as the last row -- if there were no rows at all, the values will be all NULL Erland's solution with ROW_NUMBER() is preferable from a maintainability point of view, though -- my approach is harder to initially understand and duplicates code, both of which is bad. Also, a forward ROW_NUMBER() gives you an easy way of telling whether you're on "the first few rows". Because ROW_NUMBER() with a different ORDER BY than the main ORDER BY clause requires an additional sort step, the solution with an additional FETCH may perform better, but this should not be the overriding concern -- especially not since you're already using cursors, which will probably dwarf everything else. I'm just offering it as a possible alternative. Apropos that: judicious use of ROW_NUMBER() and CTEs/subqueries may solve your problem without the use of cursors at all, which is possibly better (possibly, because cursors can actually be a faster solution for problems like these). For example, you can calculate the total amount of money you're going to be allocating to rows and then add the leftover to the last row: WITH x AS ( SELECT money * percentage AS share, CASE WHEN ROW_NUMBER() OVER (ORDER BY a DESC) = 1 THEN 1 ELSE 0 END AS last_row FROM t WHERE ... ) SELECT share + CASE last_row WHEN 1 THEN totalmoney - (SELECT SUM(share) FROM x) ELSE 0 END FROM x ORDER BY a The problem here is that the last row determination and total sum require extra passes through the data. Cursors may or may not be faster here. The query does have the benefit of expressing the logic succinctly, which improves maintainability. -- J.
|
Next
|
Last
Pages: 1 2 Prev: delete a record Next: SQL Server administration scritpts for a new server? |