Post reply

How to assign sql parameter with many values returned from SQL statement

  • December 4, 2011 at 4:59 pm

    #150689

    Hi everybody,

    I want to assign sql parameter called @myParam with many values returned from SQL statement like that:

    declare @param text

    set @param = select * from MyTable

    but it give me error, how can i do that please

    any reply will be appreciated

    Thanks

  • December 4, 2011 at 5:03 pm

    #1416445

    mos-prog (12/4/2011)

    Hi everybody,

    I want to assign sql parameter called @myParam with many values returned from SQL statement like that:

    declare @param text

    set @param = select * from MyTable

    but it give me error, how can i do that please

    any reply will be appreciated

    Thanks

    Let me make sure what you are asking, you want every column of every row returned in a single variable?

    Why? What purpose does this serve?

  • December 4, 2011 at 5:11 pm

    #1416447

    Thanks for your replay,

    I want to send an Email from Database mail, and i want to get some data from Database to send it with email to the Users,

    I want to assign @param with data to put it in @body variable that will send by Email like that:

    @body = @param

    I hope that be clear for you

    thanks

  • December 4, 2011 at 5:18 pm

    #1416449

    There are several methods to accomplish the same goal. If you lookup sp_send_dbmail in Books Online, you'll find a parameter for the sproc called "@Query". Here's a description of that from BOL...

    [font="Arial Black"][ @query = ] 'query'[/font]

    Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

    Check out sp_send_dbmail in Books Online for more details.

    --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)
    Intro to Tally Tables and Functions

  • December 4, 2011 at 5:24 pm

    #1416450

    Jeff Moden (12/4/2011)

    There are several methods to accomplish the same goal. If you lookup sp_send_dbmail in Books Online, you'll find a parameter for the sproc called "@Query". Here's a description of that from BOL...

    [font="Arial Black"][ @query = ] 'query'[/font]

    Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

    Check out sp_send_dbmail in Books Online for more details.

    Thanks man,

    but my problem with @quary is how to included it with the body of the e-mail message not be attached as a file

  • December 4, 2011 at 5:31 pm

    #1416451

    Sorry, but i face one problem as it is my first time i deal with Database Mail,

    when i try to send the Email it give me error with the sql statement in @quary,

    it give me error that "Invalid object name 'DataBaseName.Users"

    that is my try:

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'myProfile',

    @recipients = 'myEmail@hotmail.com',

    @subject = 'SQL Report',

    @body_format = 'HTML',

    @body = '.............',

    @query = 'select [DataBaseName].[Users].[UserName] from [DataBaseName].[Users]',

    @attach_query_result_as_file = 0

  • December 4, 2011 at 6:08 pm

    #1416456

    mos-prog (12/4/2011)

    Sorry, but i face one problem as it is my first time i deal with Database Mail,

    when i try to send the Email it give me error with the sql statement in @quary,

    it give me error that "Invalid object name 'DataBaseName.Users"

    that is my try:

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'myProfile',

    @recipients = 'myEmail@hotmail.com',

    @subject = 'SQL Report',

    @body_format = 'HTML',

    @body = '.............',

    @query = 'select [DataBaseName].[Users].[UserName] from [DataBaseName].[Users]',

    @attach_query_result_as_file = 0

    Try changing:

    select [DataBaseName].[Users].[UserName] from [DataBaseName].[Users]

    to

    select [UserName] from [DataBaseName].[Users]

  • December 4, 2011 at 7:36 pm

    #1416459

    mos-prog (12/4/2011)

    Jeff Moden (12/4/2011)

    There are several methods to accomplish the same goal. If you lookup sp_send_dbmail in Books Online, you'll find a parameter for the sproc called "@Query". Here's a description of that from BOL...

    [font="Arial Black"][ @query = ] 'query'[/font]

    Is a query to execute. The results of the query can be attached as a file, [font="Arial Black"]or included in the body [/font]of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

    [font="Arial Black"]Check out sp_send_dbmail in Books Online for more details.[/font]

    Thanks man,

    but my problem with @quary is how to included it with the body of the e-mail message not be attached as a file

    Gosh man, read Books Online on the subject of sp_send_dbmail. It'll tell you the other setting that needs to be made to do what you ask. 😉 You've gotta have some of the fun here. :w00t:

    --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)
    Intro to Tally Tables and Functions

  • December 4, 2011 at 7:40 pm

    #1416460

    Lynn Pettis (12/4/2011)

    mos-prog (12/4/2011)

    Sorry, but i face one problem as it is my first time i deal with Database Mail,

    when i try to send the Email it give me error with the sql statement in @quary,

    it give me error that "Invalid object name 'DataBaseName.Users"

    that is my try:

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'myProfile',

    @recipients = 'myEmail@hotmail.com',

    @subject = 'SQL Report',

    @body_format = 'HTML',

    @body = '.............',

    @query = 'select [DataBaseName].[Users].[UserName] from [DataBaseName].[Users]',

    @attach_query_result_as_file = 0

    Try changing:

    select [DataBaseName].[Users].[UserName] from [DataBaseName].[Users]

    to

    select [UserName] from [DataBaseName].[Users]

    I believe we need a schema name in that...

    select [UserName] from [DataBaseName].[[font="Arial Black"]dbo[/font]].[Users]

    --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)
    Intro to Tally Tables and Functions

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

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