Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81

    Unanswered: Set based alternative of cursors?

    Hey all,
    I am spinning my wheels to get this thing resolved unable to do that. How can i convert this code into set based or without cursors code.

    declare cursor2 cursor for
    select File_Name, File_Title_Txt, Upd_Mtch_tx, UpdateInd, AddInd, DeleteInd, AnyUpdateInd from File_Object_Table
    where TableName = ''' + @Table + '''
    and Criteria = ''' + @Criteria + '''
    and File_Title_Txt = ''' + @Title + '''

    declare @File_Name varchar(50)
    declare @File_Var1 varchar(50)
    declare @File_Var2 varchar(500)
    declare @File_Var3 bit
    declare @File_Var4 bit
    declare @File_Var5e bit
    declare @File_Var6 bit

    open cursor2
    fetch next from cursor2 into @File_Name, @File_Var1, @File_Var2, @File_Var3, @File_Var4, @File_Var5e, @File_Var6

    WHILE (@@fetch_status = 0)
    begin

    if @File_Var6 = 1
    begin
    exec (''INSERT ConfirmTable SELECT ''''' + @fileId + ''''', A.UnitNbr, ''''' + @Title + ''''', '' + @File_Var1 + '', A.'' + @File_Name + '', B.'' + @File_Name + '', ''''U''''
    from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.ActivityStatusCd = ''''U'''' '' + @File_Var2+ ''
    where A.ActivityStatusCd = ''''O'''' and B.ActivityStatusCd = ''''U'''''')

    end
    fetch next from mtcursor2 into @File_Name, @File_Var1, @File_Var2, @File_Var3, @File_Var4, @File_Var5e, @File_Var6
    end

    close cursor2
    deallocate cursor2
    Last edited by musman; 06-16-08 at 11:58.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    What is typically in @file_var2? The constructed statement does not look like it would compile.

  3. #3
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Quote Originally Posted by MCrowley
    What is typically in @file_var2? The constructed statement does not look like it would compile.
    I have corrected the stored proc, for @file_var2 and @file_var3. Take a look now please.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    INSERT INTO mytable99 (field1, field2, field3)
    SELECT field_a
         , field_b
         , field_c
    FROM   mytable98
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    in this statement:
    Code:
    exec (''INSERT ConfirmTable SELECT ''''' + @fileId + ''''', A.UnitNbr, ''''' + @Title + ''''', '' + @File_Var1 + '', A.'' + @File_Name + '', B.'' + @File_Name + '', ''''U''''
    from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.ActivityStatusCd = ''''U'''' '' + @File_Var2+ ''
    where A.ActivityStatusCd = ''''O'''' and B.ActivityStatusCd = ''''U'''''')
    It appears that @File_Name corresponds to a column name in the temp table. but @File_Var2 is just hanging off the end of the FROM clause. Is this intended?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I wish there was some way of, I don't know, copying and pasting a script exactly as it looks to avoid typing errors!

    There's a feature that's definately missing from these modern operating systems.
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Quote Originally Posted by MCrowley
    in this statement:
    Code:
    exec (''INSERT ConfirmTable SELECT ''''' + @fileId + ''''', A.UnitNbr, ''''' + @Title + ''''', '' + @File_Var1 + '', A.'' + @File_Name + '', B.'' + @File_Name + '', ''''U''''
    from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.ActivityStatusCd = ''''U'''' '' + @File_Var2+ ''
    where A.ActivityStatusCd = ''''O'''' and B.ActivityStatusCd = ''''U'''''')
    It appears that @File_Name corresponds to a column name in the temp table. but @File_Var2 is just hanging off the end of the FROM clause. Is this intended?
    Its a data(a condition that is stored in a table).

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    so you have snippets of sql code in tables?

Posting Permissions

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