Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    28

    Unanswered: 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

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Personally I think you are o ver-thonking this

    Post the DDL of the table and some sample data
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2009
    Posts
    28
    Brett if i hard code the values I get required results.

    --TODAY'S TYPE
    --select ColId, ColName, TbName, DbName, ColType, CapturedDate, ColLastModifiedDate from TotalColumn where captureddate = (select top 1 capturedDate from TotalDatabase order by capturedDate desc)
    -- and ColId = 229575856
    -- and ColName = 'asn_created_dt'
    -- and TbName = 'asn_rule'
    -- and DbName = 'ASN'
    --YESTERDAY'S TYPE
    --select ColId, ColName, TbName, DbName, ColType, CapturedDate, ColLastModifiedDate 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 = 229575856
    -- and ColName = 'asn_created_dt'
    -- and TbName = 'asn_rule'
    -- and DbName = 'ASN'


    Here is the table structure
    CREATE TABLE [dbo].[TotalColumn](
    [ColNumber] [bigint] IDENTITY(1,100000) NOT NULL,
    [CapturedDate] [datetime] NOT NULL,
    [ColName] [varchar](300) NOT NULL,
    [TbName] [varchar](500) NOT NULL,
    [DbName] [varchar](60) NOT NULL,
    [ColId] [int] NULL CONSTRAINT [DF_TotalColumn_ColId] DEFAULT ((10)),
    [ColType] [varchar](20) NULL,
    [ColCreationDate] [datetime] NULL,
    [ColLastModifiedDate] [datetime] NULL,
    [ColLength] [varchar](20) NULL)

    Sample Data:
    TODAY'S DATE DATA
    ColID ColName TbName DbName ColType CapturedDate ColLastMidifiedDate
    229575856 asn_created_dt asn_rule ASN datetime 2009-12-07 13:40:00.000 2009-10-19 09:14:23.490
    229575856 asn_created_user asn_rule ASN nchar 2009-12-07 13:40:00.000 2009-10-19 09:14:23.490
    229575856 asn_modified_dt asn_rule ASN datetime 2009-12-07 13:40:00.000 2009-10-19 09:14:23.490
    229575856 asn_modified_user asn_rule ASN nchar 2009-12-07 13:40:00.000 2009-10-19 09:14:23.490
    229575856 asn_Rule_Description_E asn_rule ASN varchar 2009-12-07 13:40:00.000 2009-10-19 09:14:23.490
    229575856 asn_Rule_ID asn_rule ASN int 2009-12-07 13:40:00.000 2009-10-19 09:14:23.490

    YESTERDAY'S DATE DATE
    ColID ColName TbName DbName ColType CapturedDate ColLastMidifiedDate
    229575856 asn_created_dt asn_rule ASN datetime 2009-12-02 14:38:00.000 2009-10-19 09:14:23.490
    229575856 asn_created_user asn_rule ASN nchar 2009-12-02 14:38:00.000 2009-10-19 09:14:23.490
    229575856 asn_modified_dt asn_rule ASN datetime 2009-12-02 14:38:00.000 2009-10-19 09:14:23.490
    229575856 asn_modified_user asn_rule ASN nchar 2009-12-02 14:38:00.000 2009-10-19 09:14:23.490
    Last edited by asyed01; 12-08-09 at 13:55.

  4. #4
    Join Date
    Oct 2009
    Posts
    28
    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

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,476
    Provided Answers: 11
    I have been playing around with the "Fetch" for the last 4 days

    what I have found

    the first FETCH BlaBla INTO @here,@here1

    then

    2rd one
    FETCH BlaBla INTO @here,@here1

    I found not having the Next in there work for me

    this mite help

    http://sqlpractices.wordpress.com/category/cursors/
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Posting Permissions

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