Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: SQL WHILE CURSOR and Insert problem

    Hi there,

    I am new to this forum so apologies if this has been covered before.

    I am trying to write a procedure that looks at records in one table and based on these write to another table.

    For example I have a table TMSUSER.SCHEDPOS which has records

    Empref AUTOKEY DAY1 POSITION TIME
    8888 1 1 ABU001 0
    8888 2 2 ABU007 840

    I want insert into TMSUSER.TMSCLK records based on this table.
    ie From a set date @@PERIODSTART add DAY1 to it and insert values.

    The code I was trying is

    declare @@uniquekey float
    declare @@day1 float
    declare @@empref varchar (20)
    declare @@clkdt datetime
    declare @@periodst datetime

    set @@periodst='09/19/2011 00:00:00 AM'
    set @@empref='8888'

    DECLARE SchedPos CURSOR FOR
    SELECT day1
    FROM tmsuser.schedpos
    WHERE day1<15 and empref=@@empref;
    OPEN SchedPos;

    FETCH NEXT FROM SchedPos;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @@uniquekey=((SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ))+ cast(@@empref as float))*100000000000
    set @@uniquekey = @@uniquekey/1000
    set @@uniquekey = round(@@uniquekey,0)

    set @@clkdt = @@periodst + (select DAY1 from tmsuser.schedpos)

    FETCH NEXT FROM SchedPos;
    insert into tmsuser.tmsclk (empref,clkdt,uniquekey)
    values
    (@@empref,@@clkdt,@@uniquekey)


    END;
    CLOSE SchedPos;
    DEALLOCATE SchedPos;
    GO



    The problem I am having is in this line
    set @@clkdt = @@periodst + (select day1 from tmsuser.schedpos)

    It is pulling both records from the table and giving me the error

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    How can I set it to just look at that DAY1 record that it is looking at in the loop?

    Thanks,

    Conor

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    cocono1, I think this will get you the same thing with out the cursor, looping and multiple row problems.

    However, since I don't have access to SQL Server right now (and TSQL is not my best SQL variation), I am not sure if you can add a number to a timestamp. Since that is what you were doing, I just left it that way.

    PS I also replaced all the @@ with @ as I don't think it is a good idea to use @@ when SQL Server uses them for its system variables. If MS decided to make @@UNIQUEKEY a reserved word, you would have to change your (original) code.
    Code:
    DECLARE @EMPREF   VARCHAR (20)
    DECLARE @PERIODST DATETIME
    
    SET @EMPREF   = '8888'
    SET @PERIODST = '09/19/2011 00:00:00 AM'
    
    
    INSERT INTO TMUSER.TMSCLK (EMPREF, CLKDT, UNIQUEKEY)
      SELECT EMPERF
           , @PERIODST + DAY1
           , ROUND (
                      (
                         ( 
                            (
                             RAND( 
                                     (DATEPART(mm, GETDATE()) * 100000 ) 
                                   + (DATEPART(ss, GETDATE()) *   1000 ) 
                                   +  DATEPART(ms, GETDATE())          
                                 ) +  CAST(EMPERF AS FLOAT)
                            ) * 100000000000
                         )  / 1000
                      ), 0
                   )
      FROM TMSUSER.SCHEDPOS
      WHERE DAY1   < 15 
        AND EMPREF = @EMPREF

  3. #3
    Join Date
    Sep 2011
    Posts
    44
    Thanks Stealth,

    I had tried this earlier but get the below are there are more than one record to import - hence when I was trying the Cursor

    Msg 512, Level 16, State 1, Procedure TAS_CLOCKINGS, Line 21
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.

    Thanks for the variable naming tip. Hadn't considered that!

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    cocono1, I don't understand. There is no subquery in what I posted so you shouldn't get the error you showed. You are allowed to do a multi-row insert.

  5. #5
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: SQL WHILE CURSOR and Insert problem

    I don't use cursors often, but, would the following be helpful?...

    FETCH NEXT FROM SchedPos INTO @@day1;

    WHILE @@FETCH_STATUS = 0
    BEGIN
    set @@uniquekey=((SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) ))+ cast(@@empref as float))*100000000000
    set @@uniquekey = @@uniquekey/1000
    set @@uniquekey = round(@@uniquekey,0)

    set @@clkdt = @@periodst + @@day1;

    FETCH NEXT FROM SchedPos INTO @@day1;

  6. #6
    Join Date
    Sep 2011
    Posts
    44
    Thanks HOMEROW.
    I got this resolved yesterday with the

    "FETCH NEXT FROM SchedPos INTO @@day1;"

    option.

    I posted here but it seems not to have saved.

    STEALTH - the reason I was getting the subquery error was because there was a trigger on the TMSCLK table that required the information being fed through one by one.

    THanks guys

  7. #7
    Join Date
    Sep 2011
    Posts
    2
    First thing here:
    declare @@day1 float
    do you really need it to be global? Use only one @ if the variable is local.

    Second, your cursor is global also.

    Third, you really should make the fetch to the variable so that the value can be assigned.

    FETCH NEXT FROM SchedPos into @day1

    4th- the @day1 variable is a float
    When you do this:

    set @@clkdt = @@periodst + (select DAY1 from tmsuser.schedpos)

    Are you trying to add a number of days, months or years to your date?
    maybe it is better for you do use the dateadd function, something like that, presuming that you are trying to add days

    select DATEADD(d, (select DAY1 from tmsuser.schedpos),@periodst )

    And, last thing, you have a subquery exactly in this line. As soon as you are going to add lines to this table, you cannot use the subquery like this. Add a scalar function, like sum, max or avg

    set @@clkdt = @@periodst + (select SUM(DAY1) from tmsuser.schedpos)

    or else put a where clause so that only one line can be returned.


    Hope i helped.

  8. #8
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    You can use merge statements of sql server 2008 instead of using cursor

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •