Solved

XP_SENDMAIL and Passing Query Parameters

Posted on 2004-09-27
2
496 Views
Last Modified: 2010-10-11
Hi,

I have the following in a sp:

select @query_email= 'select column_1, column_2 from my_table where column_1='@parameter''
EXEC master.dbo.xp_sendmail @recipients = @email,
   @query = @query_email

The @parameter is defined earlier (correctly and also the @email too).

How can I run the query in xp_sendmail and also pass a parameter from the sp into the query?

Thanks,

Farawayman
0
Comment
Question by:farawayman
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 12160105
You have to put the parameter VALUE inside the @query string, because xp_sendmail won't know about it otherwise:
select @query_email= 'select column_1, column_2 from my_table where column_1=''' + @parameter + ''''

Please take care that the @parameter value is "secure", so that it cannot contain malicious code.
To be sure the above works also if @parameter contains a ' in it (like Mc'Donald for names), use this

select @query_email= 'select column_1, column_2 from my_table where column_1=''' + replace(@parameter, '''', '''''') + ''''

CHeers
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question