Solved

XP_SENDMAIL and Passing Query Parameters

Posted on 2004-09-27
2
494 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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now