#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    5
    Rep Power
    0

    problem with cursor


    Hi,

    This is an example of the data:
    Date : 2003-08-29 16:31:42.000
    ABA: 001
    LineNumber: 1
    Text: this is test1

    The table contains different dates and line numbers can go from 1 to ...

    When the user enters a text like '%test1%', the entire report of that date should be returned. I'm using a cursor to do the job, but can't make it work right. Can someone give me some pointers?

    CREATE PROCEDURE Test
    @date datetime,
    @aba char(9),
    @text VARCHAR(25)
    As

    CREATE TABLE #TEMP2
    (
    FORMAT_D datetime,
    ABA_R char(9),
    LINE_R int ,
    REPORT_X varchar(163)
    )
    DECLARE @NEWREPORT VARCHAR(136)

    DECLARE REPORT_CURSOR CURSOR LOCAL
    FOR
    SELECT REPORT_X
    FROM CAT_ACTIVITY_REPORT
    WHERE REPORT_X LIKE '%' + @text + '%'

    OPEN REPORT_CURSOR

    FETCH NEXT FROM REPORT_CURSOR INTO @NEWREPORT

    WHILE @@FETCH_STATUS = 0
    BEGIN
    print @NEWREPORT
    insert into #TEMP2
    select
    FORMAT_D,
    ABA_R,
    LINE_R,
    REPORT_X

    from CAT_ACTIVITY_REPORT
    where

    FORMAT_D >=@date + '00:00:00'
    AND FORMAT_D < dateadd(day,1,@date) + '00:00:00'
    AND ABA_R = @aba

    FETCH NEXT FROM REPORT_cursor
    INTO @NEWREPORT

    END
    CLOSE REPORT_CURSOR
    DEALLOCATE REPORT_CURSOR

    SELECT * FROM #TEMP2

    Thanks for your help.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    21
    Rep Power
    0
    what error does it give?

IMN logo majestic logo threadwatch logo seochat tools logo