March 9, 2006 at 2:32 pm
I'm trying to call one stored procedure which gets an e-mail address from a table. I want to pass the result of that query to another stored procedure as one of the parameters.
Some of the code follows:
CREATE PROCEDURE [dbo].[sp_mail_test]
@bodyfile VARCHAR(8000), @subj VARCHAR(8000)
AS
declare @rc int
declare @adminaddress VARCHAR(8000)
exec @adminaddress = dbo.sp_get_admin_email
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'SQLServer@viasystems.com',
@FROM_NAME = N'SQL Server',
@TO = @adminaddress,
@subject = @subj,
@messagefile = @bodyfile,
@type = N'text/plain',
@server = N'172.27.4.214'
select RC = @rc
GO
My code above obviously doesn't work. It passes in the actual value '@adminaddress' into the second stored procedure dbo.xp_smtp_sendmail. What I'd like to do is pass in the result of sp_get_admin_email as a parameter.
Can anyone help me with the syntax? I've run the simple sp_get_admin_email separately and it returns exactly what I want.
Thanks.
March 9, 2006 at 2:36 pm
>>I've run the simple sp_get_admin_email separately and it returns exactly what I want.
What is it returning ? Is it a resultset that you can view ?
To assign the results to a variable, you should pass that variable in as an OUTPUT parameter, and change sp_get_admin_email to select into the OUTPUT variable instead of selecting a viewable resultset.
March 9, 2006 at 3:09 pm
Any chance you could give a quick example of the proper syntax?
Here is the crux of my sp_get_admin_email:
SELECT Admin_email FROM tblSettings
I've tried something along these lines, but it still isn't giving me what I want:
CREATE PROCEDURE [dbo].[sp_get_admin_email] (@address VARCHAR(100) OUTPUT)
AS
SELECT @address = Admin_email from tblSettings
GO
Perhaps that stored procedure is correct, but in that case I don't know the syntax for getting/using the result.
Thanks.
March 9, 2006 at 4:04 pm
EXEC [dbo].[sp_get_admin_email] @address OUTPUT
_____________
Code for TallyGenerator
March 10, 2006 at 6:42 am
OK, here's the syntax I have:
sp_get_admin_email:
CREATE PROCEDURE [dbo].[sp_get_admin_email] (@address VARCHAR(100) OUTPUT)
AS
SELECT @address = Admin_email from tblSettings
GO
sp_mail_test:
CREATE PROCEDURE [dbo].[sp_mail_test]
@bodyfile VARCHAR(8000), @subj VARCHAR(8000)
AS
declare @rc int
declare @addy VARCHAR(100)
EXEC [dbo].[sp_get_admin_email] @addy OUTPUT
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'SQLServer@test.com',
@FROM_NAME = N'SQL Server',
@TO = @addy,
@subject = @subj,
@messagefile = @bodyfile,
@type = N'text/plain',
@server = N'172.27.4.214'
select RC = @rc
GO
When I run it, I still seem to be passing the actual string '@addy' instead of the e-mail address (pulled from sp_get_admin_email) to the xp_smtp_sendmail stored procedure. That's what I see in the trace when the SP is run.
Just need to figure out the correct syntax to get the address from one SP and passed into the other.
March 10, 2006 at 7:05 am
OK, I guess it is working. Perhaps the trace doesn't show the contents of the variable @addy above. The e-mail went through which would seem to indicate the SPs are functioning as designed.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply