Sql server

  • I write an stored procedure...

    the select statement returns more than one value.. how i assign that to a variable .. is it possible

  • If all the values are the same then you could use group by.

    CREATE TABLE #tmp (f1 INT)

    INSERT INTO#tmp VALUES (1)

    INSERT INTO#tmp VALUES (1)

    INSERT INTO#tmp VALUES (1)

    --SELECT * FROM #tmp T

    DECLARE @myval INT

    SELECT @myval = f1 FROM #tmp GROUP BY f1

    PRINT @myval

  • If you're assigning variables from a SELECT statement you just do this:

    DECLARE @FirstVariable int, @SecondVariable int, @ThirdVariable int

    SELECT @FirstVariable = FirstColumn,

    @SecondVariable = SecondColumn,

    @ThirdVariable = ThirdColumn

    FROM MyTable

    But, that query can only return a single row. If it returns multiple rows, only the last one returned will end up in the variables.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • usharani.t (4/21/2011)


    I write an stored procedure...

    the select statement returns more than one value.. how i assign that to a variable .. is it possible

    What you are trying to do with that stored procedure?

    Why you need to get only one value is the requriment is like that?

    Why do you want to assign to a variable?

    create procedure proc1 (<input>)

    as

    Set Nocount ON

    Begin

    Select Top 1 * from your_table where condition=<input>

    order by <your condition> asc/desc

    End

    Set Nocount OFF

    Thanks
    Parthi

  • Thanks For you reply.......

  • Thanks For Your Reply......

  • Thanks For Your Reply......

  • usharani.t (4/21/2011)


    Thanks For Your Reply......

    I think for three members you replied 3-Times thanks 🙂

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Grant Fritchey (4/21/2011)


    If it returns multiple rows, only the last one returned will end up in the variables.

    I formed a habit from the beginning to explicitly grab one row when assigning into variables so I could be sure to get what I need, or get nothing, but this is good info for reviewing code...I never tried it on multiple rows to see which values would end up in the variables.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply