Dynamic SQL issue within cursor

  • Hello experts,

    I’m trying to write a dynamic SQL within cursor & having a little challenge. The basic idea is to compare columnType Column between today’s date and yesterday’s date. I’ve captured today’s columnType and other columns which I need (within cursor). To get yesterday’s columnType I’m tying to write a select statement. Furthermore I’m using a few variables to hold the value of curser. Now when I try to write SQL query using these variables and hold yesterday’s columnType value within variable @ColTypeYesterday I don’t see any value. However all the variables which I’m using in SQL to capture yesterday date contains value. Can anybody see what the problem is and how I can fix the mistake?

    For reference here is my code

    -----------------------------------------------------------------------------------------------

    --Variables to hold cursor's data FOR TODAY

    Declare @ColNumber bigint,

    @CapturedDate datetime,

    @ColName varchar(300),

    @TbName varchar(500),

    @DbName varchar(60),

    @ColId int,

    @ColTypeToday varchar(20),

    @ColLastModifiedDate datetime,

    @ColLengthToday varchar(20),

    --Hold yesterday's ColumnType & ColumnLength

    @ColTypeYesterday varchar(20),

    @ColLengthYesterday varchar(20)

    --Cusror to hold Modified Data info FOR TODAY

    Declare ColumnModified_Cur Cursor

    For

    select ColNumber, CapturedDate, ColName, TbName, DbName, ColId, ColType, ColLastModifiedDate, ColLength

    from TotalColumn

    where CapturedDate = (select top 1 capturedDate from TotalDatabase order by capturedDate desc)

    and ColLastModifiedDate >= (select top 1 capturedDate from TotalDatabase order by capturedDate desc)

    Open ColumnModified_Cur

    Fetch Next from ColumnModified_Cur

    Into @ColNumber, @CapturedDate, @ColName, @TbName, @DbName, @ColId, @ColTypeToday, @ColLastModifiedDate, @ColLengthToday

    while @@FETCH_STATUS = 0

    begin

    print @ColNumber

    print @CapturedDate

    print @ColName

    print @TbName

    print @DbName

    print @ColId

    print @ColTypeToday

    print @ColLastModifiedDate

    print @ColLengthToday

    print '---------------------------------'

    --Captured Yesterday's ColType

    --‘THIS IS WHERE I’M NOT GETTING VALUE. PLEASE HELP’

    select @ColTypeYesterday = ColType

    from TotalColumn

    where captureddate = (select top 1 capturedDate from TotalDatabase where capturedDate != (select top 1 capturedDate from TotalDatabase order by capturedDate desc) order by capturedDate desc)

    and ColId = @ColId

    and ColName = @ColName

    and TbName = @TbName

    and DbName = @DbName

    print '************************************************'

    print @ColTypeToday

    print @ColTypeYesterday

    print '************************************************'

    Fetch Next from ColumnModified_Cur

    Into @ColNumber, @CapturedDate, @ColName, @TbName, @DbName, @ColId, @ColTypeToday, @ColLastModifiedDate, @ColLengthToday

    end

    CLOSE ColumnModified_Cur

    Deallocate ColumnModified_Cur

    -----------------------------------------------------------------------------------------------

    Thanks in advance

  • Hey folks,

    So seems like I found the problem, when I write the following SQL I get value

    select @ColTypeYesterday = (ColType)

    from TotalColumn

    where captureddate = (select top 1 capturedDate from TotalDatabase

    where capturedDate != (select top 1 capturedDate from TotalDatabase order by capturedDate desc)

    order by capturedDate desc)

    and ColId = @ColId

    But then I execute the following SQL I don’t get any value in @ColTypeYesterday variable.

    select @ColTypeYesterday = (ColType)

    from TotalColumn

    where captureddate = (select top 1 capturedDate from TotalDatabase

    where capturedDate != (select top 1 capturedDate from TotalDatabase order by capturedDate desc)

    order by capturedDate desc)

    and ColId = @ColId

    and ColName = @ColName

    Can’t I use two AND in query like this? Seems like it doesn’t work here. How else can I use it because I’ve checked all these variables () has the value

    Thanks a lot in advance

  • Hi,

    do not exactly what output you need. The statement below returns all columns from table TotalColumn order by CapturedDate from new to old. If the statement covers not your needs you can extent this also by bilding a statement around of this like

    SELECT * FROM (SELECT * FROM) AS tmp

    SELECT tc.ColNumber, tc.CapturedDate, tc.ColName, tc.TbName, DbName, tc.ColId, tc.ColType,

    tc.ColLastModifiedDate, tc.ColLength

    FROM TotalColumn tc

    INNER JOIN TotalDatabase tb ON tc.CapturedDate = tb.CapturedDate

    AND tc.ColLastModifiedDate >= tb.CapturedDate

    ORDER BY tc.CapturedDate DESC

    I think a cursor is in that case not necessary because you can get all data with simple SQL statements. Avoid using cursor if you can get the same by using SQL statements.

    What you are using is not dynamic SQL. Dynamic SQL is a string containing a SQL statement and will be run with sp_executesql procedure.

    Regards,

    Thomas

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply