Post reply

Result to a variable

  • May 18, 2012 at 2:06 am

    #256141

    Hi,

    Is it possible to store the results into a variable, not top1, but all the results.

    Thanks inadvance

  • May 18, 2012 at 2:51 am

    #1489525

    No, a single variable can store single value. A result set with more than one rows and columns returned from a table can not be put into a single variable.

    Ryan
    //All our dreams can come true, if we have the courage to pursue them//

  • May 18, 2012 at 2:58 am

    #1489526

    Yes, it is possible. You can declare variable of a table type (known as table variable).

    You will be able to insert all results into this "variable".

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • May 18, 2012 at 4:23 am

    #1489558

    Yes, in table variable result set can be stored.

    Ryan
    //All our dreams can come true, if we have the courage to pursue them//

  • May 18, 2012 at 6:05 am

    #1489594

    You can do it using Table Variables. The following link might be helpful for you:

    Table Variables

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • May 18, 2012 at 6:15 am

    #1489598

    You can store Temporary result it either in

    Variable Table

    Declare @Emp table (EmpID ,DepId)

    Temporary Table

    Create table #Emp (EmpID ,DepId)

    Global Temporary Table

    Create table ##Emp (EmpID ,DepId)

    Variable Table and Temporary table is local to Session and Global table can be accessed by other connections

    Usually, variable table can be used if rows are below 20,000

  • May 18, 2012 at 10:13 am

    #1489804

    You can also set up a table valued parameter for passing between stored procedures and UDF's.

  • May 18, 2012 at 11:58 am

    #1489874

    Hi,

    Thanks for the reply, actually my job is to mail the results to the recipient thru DBmail ,so i want to store the result to a variable and send it as message.Is there any other way to do so?

    Thanks in advance

  • May 18, 2012 at 1:24 pm

    #1489921

    The easiest way is simply to use these parameters from send_dbmail.

    @query = 'SELECT * FROM @tablevariable'

    ,@attach_query_result_as_file = 1

    http://msdn.microsoft.com/en-us/library/ms190307.aspx

  • May 19, 2012 at 9:36 am

    #1490129

    mirravi (5/18/2012)

    Hi,

    Thanks for the reply, actually my job is to mail the results to the recipient thru DBmail ,so i want to store the result to a variable and send it as message.Is there any other way to do so?

    Thanks in advance

    "The results". Result of what? If you were to post the code that generated the results and the actual results (or facsimili of the results... don't post any sensitive or private information), there are some REALLY cool ways to email "the results" that we might be able to show you.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 19, 2012 at 9:43 am

    #1490130

    Ryan007 (5/18/2012)

    No, a single variable can store single value. A result set with more than one rows and columns returned from a table can not be put into a single variable.

    As someone already stated, you have table variables that might do the job. But, if we stretch our imagination a bit, you can store hundreds of thousands of "values" in a single variable using XML, delimited text, fixed field text, HTML, etc, etc. As with anything else, "It Depends" on what needs to be done. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 19, 2012 at 10:53 am

    #1490138

    Hi,

    Thanks for the reply.results are the like, an select query runs for every 3 hrs(as a job) and the result of the query has to be send to the recipient .There are about 10 rows displayed when query runs.

  • May 19, 2012 at 4:48 pm

    #1490159

    Then the partial answer that MysteryJimbo gave should do the trick for you. Just change out the sample query he used with yours. And, yeah, the query could be an EXEC of a stored procedureso long as the sproc returns just 1 result set.

    If you Google for sp_send_dbmail and look at the MS definition for it (http://msdn.microsoft.com/en-us/library/ms190307(v=sql.100).aspx), you also find some examples. Example "B" is like what we've just described. Example "C" is really cool because you can easily format the output so that it looks like an embedded spreadsheet.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 13 (of 13 total)

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