Solved

XP_SENDMAIL and Passing Query Parameters

Posted on 2004-09-27
2
499 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 143

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

685 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