| Author |
 Topic  |
|
|
jp2code
Posting Yak Master
USA
175 Posts |
 Posted - 09/25/2008 : 15:59:17
|
How would I convert the results of a string query in a stored procedure to text?
We made a crude SQL Mailer for failed parts. When we call it, we simply input the part number.
What we want is for our mailer to collect the records for this part, and use this as the body in our text message.
How would I convert the query result into a text format?
(Once we get it working, we can start cleaning it up)
Here's the short version of what I have now:
CREATE PROCEDURE sp_sendMail (@From varchar(100), @To varchar(100), @Subject varchar(15)) AS
---
Declare @imsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @body varchar(4000)
---
exec @body = sp_GetRecord @Subject
exec @hr = sp_OACreate 'cdo.message', @imsg out
exec @hr = sp_OASetProperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value', '2'
exec @hr = sp_OASetProperty @imsg,'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'localhost'
exec @hr = sp_OAMethod @imsg, 'configuration.fields.UPDATE', NULL
exec @hr = sp_OASetProperty @imsg, 'To', @To
exec @hr = sp_OASetProperty @imsg, 'From', @From
exec @hr = sp_OASetProperty @imsg, 'Subject', @Subject
exec @hr = sp_OASetProperty @imsg, 'TextBody', @body -- can replace HtmlBody with HtmlBody
exec @hr = sp_OAMethod @imsg, 'Send', NULL
exec @hr = sp_OADestroy @imsg
--
GO
Avoid Sears Home Improvement |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jp2code
Posting Yak Master
USA
175 Posts |
Posted - 09/26/2008 : 09:17:14
|
See this line:
exec @body = sp_GetRecord @Subject
Now I know that @body is a varchar array and the sp_GetRecord query is returning ...well, not that! What does it return? A result set? A data table? Maybe if I knew what it returned, I'd know how to collect it.
Avoid Sears Home Improvement |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
|
|
jp2code
Posting Yak Master
USA
175 Posts |
Posted - 09/26/2008 : 12:40:41
|
No one seems to understand what I'm asking because no one there or here has shown me how to get the returned value from a query into a variable. All the example shows is how to get a count. I want all of the records returned in something like a string array.
Using the result set in the link everyone keeps pointing me to:
CREATE PROCEDURE dbo.GetPeopleByLastName (@LastName NVARCHAR(50))
AS
SELECT ContactID,
FirstName,
LastName
FROM Person.Contact
WHERE LastName = @LastName
ORDER BY ContactID
Now, I want to get all of this data:
EXEC dbo.GetPeopleByLastName @LastName = 'Alexander'
And use it as a field in an email body that will be sent out.
How do I turn the result into a text field?
Does anyone understand what I want? If so, is it possible?
Avoid Sears Home Improvement |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48118 Posts |
Posted - 09/26/2008 : 12:46:17
|
quote:
Originally posted by jp2code
No one seems to understand what I'm asking because no one there or here has shown me how to get the returned value from a query into a variable. All the example shows is how to get a count. I want all of the records returned in something like a string array.
Using the result set in the link everyone keeps pointing me to:
CREATE PROCEDURE dbo.GetPeopleByLastName (@LastName NVARCHAR(50))
AS
SELECT ContactID,
FirstName,
LastName
FROM Person.Contact
WHERE LastName = @LastName
ORDER BY ContactID
Now, I want to get all of this data:
EXEC dbo.GetPeopleByLastName @LastName = 'Alexander'
And use it as a field in an email body that will be sent out.
How do I turn the result into a text field?
Does anyone understand what I want? If so, is it possible?
Avoid Sears Home Improvement
you mean all the fields as a single text?
as comma seperated values?
Also are you using sql 2005? |
|
|
|
jp2code
Posting Yak Master
USA
175 Posts |
Posted - 09/26/2008 : 13:09:16
|
SQL 2000.
Comma Seperated Values or Single Text - I don't care really. I just want to see what I can get it to return. It may look really bad, in which case we'd have to find another way - like passing in the complete table as the message body.
Avoid Sears Home Improvement |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48118 Posts |
Posted - 09/26/2008 : 13:32:37
|
is this what you want?
CREATE FUNCTION dbo.GetPeopleByLastName
(@LastName NVARCHAR(50))
AS
BEGIN
DECLARE @text VARCHAR(8000)
SELECT @text = COALESCE(@strOutput + ',', '') + CAST(ContactID AS varchar(10)) + ','+FirstName + ',' + LastName
FROM Person.Contact
WHERE LastName = @LastName
ORDER BY ContactID
RETURN @text
END
GO
then use it like this
DECLARE @receivestring varchar(8000)
EXEC @receivestring = GetPeopleByLastName 'Alexander'
and use @receivestring for sending mail |
|
|
|
jp2code
Posting Yak Master
USA
175 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48118 Posts |
Posted - 09/27/2008 : 01:46:18
|
quote:
Originally posted by jp2code
I think that's what I'm after. Now I have to find out how to make it work!
Avoid Sears Home Improvement
why are you getting any error? |
|
|
| |
Topic |
|
Return Query Results as string
