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 14: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,423
    Provided Answers: 8
    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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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