Results 1 to 12 of 12
  1. #1
    Join Date
    May 2004
    Posts
    5

    Lightbulb Unanswered: Simplest way to get a query result into a variable

    Hello,

    I am interested in what the simplest was to get a query result that will only ever have one result (ie One column, one row) into a variable. An ugly way is to use a cursor that simply fetches the first row but that seems to be a horrible way to do it and it has sometimes major drawbacks sometimes (mainly if I have to dynamically choose the table). Surely there is a better way?

    What do you think? A simple example would be nice.

    Cheers
    J

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think what you need as a User Defined Function. There are two types; table and scalar, and the type you want is the scalar. Look up User Defined Funtions in books online and post again if you have more questions.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, I take that back. Probably the simplest was is this:

    set @YourVariable = (select YourSingleValue from YourTable where YourCriteria = True)
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    May 2004
    Posts
    5
    Being SQL that idea seemed too simple to work. :-)
    But I'll have a fiddle with it.
    Cheers

  5. #5
    Join Date
    May 2004
    Posts
    5
    Alright, that works but im struggling to get variables into it..

    set @YourVariable = (select YourSingleValue from @someTable where YourCriteria = True)

    But syntax will achive this? Do I have to biuld the string and do an Execute or something? I have been fiddling for a while now and it jsut seems to be some minor syntax thats getting me.

    Cheers
    j

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    DECLARE @OrderId int, @OrderDate datetime
    
    SELECT @OrderDate = '1996-07-08 00:00:00.000'
    
      SELECT TOP 1 @OrderId = OrderId 
        FROM Orders
       WHERE OrderDate = @OrderDate
    ORDER BY OrderID
    
    PRINT 'Only 1'
    
    SELECT @OrderId AS Only_One_OrderId 
    
    PRINT 'The Real Result set'
    
    SELECT OrderId 
      FROM Orders
     WHERE OrderDate = @OrderDate
    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
    May 2004
    Posts
    5
    That all makes perfect sense but I need the one single result pushed into a variable so I can use it in other places. Something along the lines of:

    set @myvariable = (SELECT TOP 1 @OrderId = OrderId FROM Orders WHERE OrderDate = @OrderDate ORDER BY OrderID)

    But obviously syntax that works!
    Cheers
    J

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    USE Northwind
    GO
    DECLARE @OrderId int, @OrderDate datetime
    
    SELECT @OrderDate = '1996-07-08 00:00:00.000'
    
      SELECT top 1 @OrderId = OrderId 
    	FROM Orders
       WHERE OrderDate = @OrderDate
    ORDER BY OrderID
    
    Select  'Only 1'
    
    SELECT @OrderId AS The_TopOne_OrderId 
    
    
    Select 'The last One'
     SELECT @OrderId = OrderId 
    	FROM Orders
       WHERE OrderDate = @OrderDate
    ORDER BY OrderID
    SELECT @OrderId AS The_LastOne_OrderId 
    
    Select 'The Real Result set'
    
    SELECT OrderId 
      FROM Orders
     WHERE OrderDate = @OrderDate
    SELECT @local_variable is usually used to return a single value into the variable. It can return multiple values if, for example, expression is the name of a column. If the SELECT statement returns more than one value, the variable is assigned the last value returned.
    Get yourself a copy of the The Holy Book

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

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Aren't you making the server do a lot more work that way?
    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.

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    I am .. but I was simply pointing out the difference if he did not use a Top 1
    Get yourself a copy of the The Holy Book

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

  11. #11
    Join Date
    May 2004
    Posts
    5

    Question

    Basically, your examples work and they make quite alot of sense but my code returns some really strange values. There is a subtle but important differnce in that I dont know the column I will need in the destination table. It needs to be given ina variable.

    I'll try to explain using some cut down code:

    ================================================== ==
    Declare @Ext as varchar (10), @DateTime as varchar (20), @InstID as varchar(5) @Result2 as money --The table data is in money format

    Set @InstID = '114'
    Set @Ext = 'BID'
    Set @DateTime = '2003/02/12 11:29:00'

    select @result2 = @ext from MyTable where collected = @DateTime and Instrument_ID = @InstID

    ***The above returns me the value of the variable @EXT which in this case is 'BID' - certainly not what I want

    select @result2 = BID from MyTable where collected = @DateTime and Instrument_ID = @InstID

    *** If I replace the variable with its actual value manually it returns me the correct value from the table which is something like '7.14'

    It seems that if a variable is based on another variable, it fails.
    Any ideas?

  12. #12
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    try looking at BOL at sp_executesql using input parameters and reading output variables

    Rgds

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

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