Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Return Query Results as string

Author  Topic 

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-25 : 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

Posted - 2008-09-25 : 16:14:59
ummm, where's the query?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-26 : 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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 09:41:17
See this for further clues
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111472



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-26 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 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?
Go to Top of Page

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-26 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 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
Go to Top of Page

jp2code
Posting Yak Master

175 Posts

Posted - 2008-09-26 : 15:36:28
I think that's what I'm after. Now I have to find out how to make it work!


Avoid Sears Home Improvement
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 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?
Go to Top of Page
   

- Advertisement -