none
retreiving multiple fields RRS feed

  • Question

  • i want to retrieve mutiple fileds using a select statement in sql query analyzer.

    for example:

    my table has columns: id,firstname,lastname

    i was trying to use the following code, but its not working

    declare @id integer

    select @id=''

    declare @firstname varchar(50)

    select @firstname=''

    declare @lastname varchar(50)

    select @lastname=''

    select @id,@firstname,@lastname=select id,min(firstname),lastname from Table1 where <Some where condition>

    Tuesday, August 23, 2011 5:25 PM

Answers

  • Try

    select
     @var1 = source_Column_1,
     @var2 = source_Column_2,
     ...
     @varN = source_Column_N
    from your_Table
    where <some where condition>
    
    

     

    Caution:

    This will fetch the values of the last row returned by your WHERE condition into the variables.  One remedy is to use a TOP 1 modifier on your query with an ORDER BY clause after the WHERE clause(s)

    It will help if you describe how you intend to apply this.

    Tuesday, August 23, 2011 5:28 PM
    Moderator

All replies

  • Try

    select
     @var1 = source_Column_1,
     @var2 = source_Column_2,
     ...
     @varN = source_Column_N
    from your_Table
    where <some where condition>
    
    

     

    Caution:

    This will fetch the values of the last row returned by your WHERE condition into the variables.  One remedy is to use a TOP 1 modifier on your query with an ORDER BY clause after the WHERE clause(s)

    It will help if you describe how you intend to apply this.

    Tuesday, August 23, 2011 5:28 PM
    Moderator
  • If you are working in SQL Server 2008 is posible to compact your code, try:

    DECLARE @id INTEGER = 0  --Value Default for INTEGER 
    DECLARE @firstname varchar(50) = ''
    DECLARE @lastname varchar(50) = ''
    
    SELECT @id=id,@firstname=firsname,@lastname=lastname,
    FROM Table1 
    where <Some where condition>
    
    
    

    Is very important the caution of Kent, you should use this type of code when you want obtain aggregates.


    "Talent is a tough discipline and a long patience"  Gustave Flaubert

    Email: info@geohernandez.com Blog: geeks.ms/blogs/ghernandez

     

    Tuesday, August 23, 2011 5:42 PM
  • i can't use an aggregate function on only 1 field? do i have to use it on all fields in the query?
    Tuesday, August 23, 2011 7:09 PM
  • Yes, for all fields which are not included into GROUP BY clause, if you use aggregate function on one field, you need to use aggregate function for all other fields.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, August 23, 2011 7:20 PM
    Moderator
  • You need to better spell out exactly what you are trying to do.

    Hello

    Welcome to the Transact SQL Forum.  Here are a few things to get you oriented.  First, give a look at these two guidelines for posting questions in the Transact SQL Forum:

    MSDN Transact SQL Forum Posting Guidelines:

       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/af20783d-2489-46e4-b6f4-be0ab6d514cb

          Posted by Clifford Dibble

          http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/ce8989b5-dc58-47d8-af65-db5bcd4f7ee2

          Posted by Phil Brammer

    Next, become aware of the code and samples that you post.  One thing that will help you get better answers for your questions is to make your source code more readable.  Therefore, this forum provides a code formatting tool.  You can find the code tool.  The code tool is identified on the tool pallet by an icon that looks like a tiny window with the symbols “</>”.  Use this tool to insert your sample code; this will help the people that read your code.

    If you are getting some kind of error condition, please provide a full description of the error condition; if you are getting an error message from SQL Server itself, please provide the exact error message is generated.

    You may use an aggregate of a column without including other columns; however, if you are going to select other non-aggregate columns as part of your select list you must do one of two things:

    • Include a GROUP BY clause or
    • Include an OVER clause as part of your aggregate
    Tuesday, August 23, 2011 7:25 PM
    Moderator