Re: Cursor

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: jduran (jduran_at_discussions.microsoft.com)
Date: 06/21/04 

Date: Mon, 21 Jun 2004 13:08:01 -0700

Hugo,
I understand all your comments about the table. But unfortunately I did not design the table, nor do I have the authority to change it. Believe me I have tried.

You are correct I forgot to include the cursor. Here it is.

CREATE PROCEDURE dbo.ssp_fhkivr5

AS

SET NOCOUNT ON

-- declare all variables!

DECLARE

@HMO_Phone varchar(15)

Declare hmophone scroll cursor
for
Select top 100 fhk_hmo_phone
from tbl_fhk_child
for UPDATE of fhk_hmo_phone

open hmophone

fetch next from hmophone into @hmo_phone

while @@fetch_status = 0

Begin
       

            

  UPDATE tbl_fhk_child
       SET @hmo_phone = counthmo.hmo_phone,
       fhk_hmo_phone = @hmo_phone
         
        FROM tbl_fhk_child, counthmo, appl
        WHERE
        counthmo.HEALTH_PLAN = appl.HEALTH_PLAN
        and appl.parent_id = tbl_fhk_child.fhk_parent_id;

   
    
  

fetch next from hmophone into @hmo_phone

end

deallocate hmophone

This cursor tells me it is Read Only.
"Hugo Kornelis" wrote:

> On Mon, 21 Jun 2004 09:06:02 -0700, jduran wrote:
>
> >Need help with a simple cursor.
>
> Hi jduran,
>
> No, you don't.
>
> First, your post doesn't have enough information to show what you are
> trying to achieve. You mention a cursor, but I see nothing that refers to
> a cursor. You give the definition for one of your tables, but not for the
> others; you don't give sample data and you don't explain what you are
> trying to achieve.
>
> Even without knowing what you are trying to do, I'm pretty sure it can be
> done without cursors. 99.9% of all things that need to be done in a
> database can be done with set-based queries, without cursors and in 99.8%
> of all cases is the set-based approach quicker and easier to maintain.
>
> If you want us to help you, include the following in your post:
> * DDL (CREATE TABLE statements) for ALL tables that are relevant to the
> problem. You may leave out the columns that are of no interest, but please
> do include all constraints!!
> * Sample data (in the form of INSERT statements - I like copy and paste
> almost as much as I dislike typing) for all relevant columns in all
> relevant tables.
> * Expected results after the query.
> * An explanation of how the supplied input leads to the expected results.
> * A description of the business problem you're trying to solve.
>
> With that, we can probably help you find an efficient, set-based solution
> for your problem.
>
> Oh, and before I forget it - you might want to look into your table
> designj first, before going any further. Your design is, in all honesty,
> quite a mess. Some questions you should ask yourself:
> * Why is every column NULLable?
> * Why are so many columns defined as nvarchar(50)? Do you really expect
> Katana characters in fhk_child_fname of fhk_state? Will your application
> really accept 50-character SSN's as input?
> * Please stop using that silly tbl_ prefix in front or your table name.
> Are you afraid that you forget it's a table? Will you actually change all
> your queries to change "tbl_" to "vw_" when the table changes and you
> create a view to ensure backwards compatibility?
> * Also, don't put fhk in front of each column name. A phone number is a
> phone number, regardless of the table it is stored in.
>
> I'll stop here, thought there's probably a lot more. Google this group for
> "Celko" + "ISO-11179" if you want to know it all (but be prepared if you
> consider me harsh - my post is as sweet as a love-letter in comparison
> with Celko on a mild day...).
>
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Relevant Pages

  • Re: A new proof of the superiority of set oriented approaches: numerical/time serie linear interpola
    ... superior, not principally due to run times, but due to inherent solidity of ... table" design as opposed to normalized, ... If you ask me I tend to think that even in direct image systems, ... And I have not found any satifying cursor ...
    (comp.databases.theory)
  • Re: Cursor loop
    ... I've created a stored procedure that loops through a cursor, ... DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriods ... The problem is that this loop only executes one time, ...
    (comp.databases.ms-sqlserver)
  • Re: Optimize function that uses cursors
    ... > The function can be made recursive as there are no much recursions (we ... > groups and ancestor groups (i.e. the parents of the parents and so on) for ... > a recursive call in the select of the cursor), and in the cursor look it ... > declare @more bit ...
    (microsoft.public.sqlserver.programming)
  • Re: Help Optimize Code
    ... Next cursor is used for all user accounts (t0, t1, ... DECLARE @name VARCHAR ... DECLARE @getAccountID CURSOR ... SELECT 't0' UNION ALL SELECT 't1' UNION ALL SELECT 't2' UNION ALL ...
    (microsoft.public.sqlserver.programming)
  • Re: Replace Cursor Procedure with Update Query?
    ... DECLARE @tmpResults TABLE, Create_Date Int, Seq int) ... Can I replace the below cursor based procedure with an update> query? ... Every morning, after the update,> I run the procedure below to update the Sequence field. ...
    (microsoft.public.sqlserver.programming)