Save table data to a string - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Save table data to a string

Author

Topic

baska123
Yak Posting Veteran

64 Posts

Posted - 2006-08-22 : 12:11:28

I want to select everything from email table (only one column) and save it to a string.. I want to do all of that in T-SQL

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-22 : 12:20:11

Where is ur data & where do u want to save ?

Srinika

baska123
Yak Posting Veteran

64 Posts

Posted - 2006-08-22 : 12:24:20

My data is in empl table. I want to select everything from this table (which are only email addresses), and then go through that table and create a string from it. I want to save it to a string @recipients.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-22 : 12:37:08

Here is the code, which u may have to modify according to ur needs (but there r limitations as it can hold only 8000 characters etc)

DECLARE @recipients VARCHAR(8000)
SELECT @recipients =''
SELECT 	@recipients=@recipients + rtrim([email_field_name]) + ' <Any Seperator or space> '
from empl
Select @recipients

What is ur final goal by doing this ?

Srinika

baska123
Yak Posting Veteran

64 Posts

Posted - 2006-08-22 : 12:52:31

I am trying to send an email to a group of recipients ex.group@city.ca, and I did not find a way of doing it using t-SQL (except for listing all the recipients individually), therefore I want to save all the emails in the table and read it from there.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-22 : 12:56:53

did my code work for u?

Srinika

baska123
Yak Posting Veteran

64 Posts

Posted - 2006-08-22 : 13:14:06

How do you concatenate strings ? Especially apostrophe ‘ ?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-22 : 14:02:33

each single apostrophe should be replaced with 2 apostrophes.

Srinika

baska123
Yak Posting Veteran

64 Posts

Posted - 2006-08-22 : 16:06:09

Still working on the code. Does not work as I hoped. Will keep you posted.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-22 : 19:21:10

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 5

there's no need to worry about ' when contencating values from db.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

baska123
Yak Posting Veteran

64 Posts

Posted - 2006-08-23 : 11:18:30

Does anybody see anything wrong with it?

CREATE PROCEDURE Intake_SendMail_SP_TEST

AS

DECLARE @Message VARCHAR(8000)
DECLARE @Subject VARCHAR(100)
DECLARE @Idea_Name CHAR(40)
DECLARE @Idea_Status CHAR(40)
DECLARE @Idea_Accountablepm CHAR(20)
DECLARE @Iea_Successmgmtreviewdate DATETIME
DECLARE @Rec_List VARCHAR(8000)
DECLARE @EmailAddress VARCHAR(50)
BEGIN

SET @Subject = ' Testing Let me know if you recieved this email BARBARA'
SET @Message = 'Dear Success Management team member,' + CHAR(13) + CHAR(13)

IF EXISTS (SELECT IDEA_NAME, IDEA_STATUS, IDEA_ACCOUNTABLEPM, IDEA_SUCCESSMGMTREVIEWDATE FROM IDEA
WHERE IDEA_SUCCESSMGMTREVIEWDATE BETWEEN GETDATE() AND DATEADD(WW,2,GETDATE()))
BEGIN
SET @Message = @Message + 'The following are the upcoming Ideas/Oppurtunities for the Success Management Review'+ CHAR(13) + CHAR(13)
SET @Message = @Message + '--------------------------------------------------------------------------------------------------------------------------' + CHAR(13)
SET @Message = @Message + 'IDEA NAME ' + ' STATUS ' + ' ACCOUNTABLE ITAM ' + ' SM REVIEW DATE ' + CHAR(13)
SET @Message = @Message + '--------------------------------------------------------------------------------------------------------------------------' + CHAR(13)
DECLARE IDEA_CURSOR CURSOR FOR
SELECT IDEA_NAME, IDEA_STATUS, IDEA_ACCOUNTABLEPM, isnull(IDEA_SUCCESSMGMTREVIEWDATE,'') FROM IDEA
WHERE IDEA_SUCCESSMGMTREVIEWDATE BETWEEN GETDATE() AND DATEADD(WW,2,GETDATE())

OPEN IDEA_CURSOR
FETCH NEXT FROM IDEA_CURSOR INTO @Idea_Name, @Idea_Status, @Idea_Accountablepm, @Iea_Successmgmtreviewdate
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @Message = @Message + CHAR(13)
SET @Message = @Message + @Idea_Name + ' ' + @Idea_Status + CHAR(9) + @Idea_Accountablepm + ' ' + CONVERT(VARCHAR(20),@Iea_Successmgmtreviewdate,101)
FETCH NEXT FROM IDEA_CURSOR INTO @Idea_Name, @Idea_Status, @Idea_Accountablepm, @Iea_Successmgmtreviewdate
END

CLOSE IDEA_CURSOR
DEALLOCATE IDEA_CURSOR
SET @Message = @Message + CHAR(13)+'--------------------------------------------------------------------------------------------------------------------------' + CHAR(13)
END
ELSE
BEGIN
SET @Message = @Message + 'There are no upcoming Ideas/Oppurtunities for Success Management Review in next two weeks.'+ CHAR(13)
END

IF EXISTS (select EmailAddress from email)
begin
DECLARE Email_Cursor CURSOR for
select EmailAddress from email

OPEN Email_Cursor
Fetch next from Email_Cursor into @EmailAddress
While @@Fetch_Status <>-1
BEGIN
set @Rec_List = @Rec_List + ';'
set @Rec_List = @Rec_List + @EmailAddress
FETCH NEXT From Email_Cursor INTO @EmailAddress
END
END

SET @Message = @Message + CHAR(13) + CHAR(13) + '***** This is a system generated message. Please do not reply. *****'

EXEC master..xp_sendmail @recipients = @Rec_List ,@message = @Message, @subject = @Subject
END
GO

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-23 : 12:12:12

This was from another thread

quote:
Originally posted by baska123

... I am still strugling with this email issue

Where r u struggling at?
u cannot send emails ?
r u not creating ur senders list, or msg body ?

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources