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

Question

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

mos-prog

SSC Veteran

Points: 270
More actions
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

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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?

mos-prog SSC Veteran Points: 270 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

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)

mos-prog SSC Veteran Points: 270 More actions · Answer 4

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

mos-prog SSC Veteran Points: 270 More actions · Answer 5

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

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)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

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)