Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    31

    Unanswered: Variable as column in Cursor Select

    I can't seem to get a cursor to work when I'm passing in a variable for a column name of the select statement. For example:

    declare @col varchar(50)

    set @col = 'Temperature'

    declare notifycurs cursor scroll for
    select @col from Table

    Obviously this won't work correctly (since the result will simply be 'Temperature' instead of the actual float value for temperature). I tried to use quotes for the entire statement with an EXEC
    (ie. exec('select '+@col+' from Table' )
    but that gave me an error.

    Is there a way to pass in a variable for a column name for a curor select statement????

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK....I can't fathom WHY you would want to do this.....
    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
    Jun 2004
    Posts
    31
    Ok. I'll try to explain this as best I can. The cursor I'm having problems with is actually an embedded cursor.

    What I have is a table (tblOutlier) that basically consists of four primary fields:
    1. weather parameters (varchar) (ie. Temperature, Wind
    Speed, etc. )
    2. high range (float)
    3. low range (float)
    4. flag code (varchar)

    The values in the weather parameters field are field names in another table (tblMet), which stores meteorological data.

    The primary objective of this procedure is to assign a flag code to records in tblMet that have a values that are out of range based on the corresponding values in tblOutlier. Once a flag code is assigned to those records in tblMet, information about that record is aquired and another stored procedure is called to send a notification email concerning the out of range values.

    Now, here is the basic logic of my code:
    declare @parameter varchar
    declare @high float
    declare @low float
    declare @thetime int
    declare @date varchar
    declare @thevalue float

    declare cursor1 for
    select weather parameter, high range, low range,
    flagcode from tblOutlier
    open cursor1
    fetch first into @parameter, @high, @low, @flag

    Update tblMet set flagcode = @flag where @parameter >
    @high or @parameter < @low



    --embedded cursor
    declare cursor2 for
    select date, time, @parameter from tblMet where
    flagcode = @flag
    open cursor2
    fetch first into @thdate, @thetime, @thevalue
    declare @message as varchar(500)
    set @message = 'the value for ' + @parameter + 'is
    out of range'
    call notification procedure ('joe@aol.com', @message)
    fetch next into @date, @thetime, @thevalue

    fetch next into @parameter, @high, @low, @flag
    Last edited by jgags; 06-07-04 at 13:17.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You mean like

    Code:
    USE Northwind
    GO
    
    DECLARE @x sysname, @SQL varchar(8000), @OrderId int
    SELECT @x = 'OrderID'
    SELECT @SQL = 'DECLARE myCursor99 CURSOR FOR SELECT ' + @x + ' FROM Orders'
    EXEC(@SQL)
    
    OPEN myCursor99
    FETCH NEXT FROM myCursor99 INTO @OrderId
    WHILE @@FETCH_STATUS = 0
      BEGIN
    	FETCH NEXT FROM myCursor99 INTO @OrderId
      END
    CLOSE myCursor99
    DEALLOCATE myCursor99
    SELECT @OrderId
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett, this one's all yours buddy.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Gee thanks....I'd figure if I'd dive hime/her enough rope...

    I mean dynamic nested cursors?

    I've actually seen 3rd part tools that used things like this...

    it was a logocall database where all field names are stroed with the data...

    Like a single vertical table...

    what a mess....and wasn't cheap....
    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.

  7. #7
    Join Date
    Jun 2004
    Posts
    31
    Brett,
    You're example code worked just fine. Thank you.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Glad it does what you need...

    That's fair amount of rope I've given you....

    be careful....
    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.

Posting Permissions

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