Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Group and batch select/cursor results in stored procedure Expand / Collapse
Author
Message
Posted Tuesday, December 1, 2009 9:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
Points: 44, Visits: 162
Created a stored procedure to look at Notes table that extracts notes, author and author's e-mail address based on criteria such as note type and number of days old. Trying to send results via e-mail using DBMail in SQL Server 2005. E-Mails should be sent to the author of the note(s).

My problem is that I want to "batch" all of the notes that meet the select criteria by author/e-mail address and sent 1 e-mail to the author that lists all of the selected notes. Example: Author Steve wrote 5 notes that meet the note type/age date criteria. Want to send 1 e-mail to
steve@company.com the lists the extracted notes data:

entrydate, notetype, author, e-mail address, companyname, contact firstname, contact lastname

In the cursor select statement, I ordered the results by e-mail address but this is not working as above example.

Everything else is working in the stored procedure except for this grouping/batching.


Attached Word document has table create script and test data - per article on how to post data/code to forum.
Stored Procedure script is below.

Stored Procedure Script

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- 2009/11/19 rss - new
-- Procedure to pull specific CRM Notes for e-mail notification

ALTER PROCEDURE [dbo].[QVI_CRMNotesNotificationSP]

AS

SET NOCOUNT ON

--- Creates temporary @tempnotes table to hold report ouput

DECLARE @tempnotes TABLE

( entrydate datetime
, notetype nvarchar(30)
, employeename nvarchar(50)
, title nvarchar(30)
, emailaddress nvarchar(255)
, companyname nvarchar(60)
, firstname nvarchar(20)
, middleinit nvarchar(1)
, lastname nvarchar(30)
, companyid uniqueidentifier
, employeeid uniqueidentifier
, icompemployeeid uniqueidentifier
)

DECLARE

@noteentrydate datetime
, @notetype nvarchar(30)
, @icompemployeename nvarchar(50)
, @icomptitle nvarchar(30)
, @icompemailaddress nvarchar(255)
, @xcompcompanyname nvarchar(60)
, @contactfirstname nvarchar(20)
, @contactmiddleinit nvarchar(1)
, @contactlastname nvarchar(30)
, @notecompanyid uniqueidentifier
, @noteemployeeid uniqueidentifier
, @icompemployeeid uniqueidentifier
, @notecontactid uniqueidentifier
, @concontactid uniqueidentifier

, @subj nvarchar(255)
, @body nvarchar(4000)
, @temp nvarchar(255)
, @to nvarchar(1000)

, @message nvarchar(4000)

DECLARE CRMNotesPullCrs CURSOR LOCAL STATIC FOR

SELECT

n.entrydate --- Notes entry date
, n.notetype --- Note Type
, i.employeename --- Note Author
, i.title --- Note Author Title
, i.emailaddress --- Note Author E-Mail address
, x.companyname --- Company/Account associated with Note
, c.firstname --- Contact associated with Note
, c.middleinit
, c.lastname

FROM dbo.NOTE_tblNote n, dbo.ICOMP_tblEmployee i, XCOMP_tblCompany x, CON_tblContact c

WHERE

n.employeeid = i.employeeid
and n.companyid = x.companyid
and n.contactid = c.contactid
----and n.notetype = 'E-Mail Sent'
and DATEDIFF(day, n.entrydate, GETDATE()) between 0 and 90 ---- variable

order by i.emailaddress

---- Start Cursor Processing

OPEN CRMNotesPullCrs WHILE 1=1

BEGIN

FETCH CRMNotesPullCrs INTO

@noteentrydate
, @notetype
, @icompemployeename
, @icomptitle
, @icompemailaddress
, @xcompcompanyname
, @contactfirstname
, @contactmiddleinit
, @contactlastname

IF @@FETCH_STATUS <> 0 BREAK

---- End Cursor Processing

BEGIN --- @tempnotes temporary table

INSERT INTO @tempnotes
(
entrydate
, notetype
, employeename
, title
, emailaddress
, companyname
, firstname
, middleinit
, lastname
, companyid
, employeeid
, icompemployeeid
)

VALUES
(
@noteentrydate
, @notetype
, @icompemployeename
, @icomptitle
, @icompemailaddress
, @xcompcompanyname
, @contactfirstname
, @contactmiddleinit
, @contactlastname
, @notecompanyid
, @noteemployeeid
, @icompemployeeid
)

END --- @tempnotes temporary table


---- new code rss 11-30-09

BEGIN --- e-mail distribution section

SET @to = @icompemailaddress

SET @subj = 'Notes Follow-up Notice'

SET @message = 'EntryDate Author E-Mail Address CompanyName FirstName LastName' + CHAR(10) + CHAR(13)

---- WHILE @@FETCH_STATUS = 0 --- (not sure if this is needed???)

BEGIN


SET @temp = (select CAST (@noteentrydate AS nvarchar(12)) ) + ' ' + @icompemployeename + ' ' + @icompemailaddress + ' ' + @xcompcompanyname + ' ' + @contactfirstname + ' ' + @contactlastname + ' ' + CHAR(10)

SET @message = @message + @temp

END --- e-mail distribution section

BEGIN --- dbmail

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Job-Mail'
, @recipients = @to
, @subject = @subj
, @body = @message
, @body_format = 'HTML'

---- 'This is a test message from CRM via SQL Database Mail. This message is a reminder to follow-up on sales calls.'

---- debug print statements
print ' '
print ' '
print 'email output start set'
print @to
print @subj
print @message
print 'email output end set'
print ' '
--- end debug print statements

END --- db mail code


END -- CRMNotesPullCrs
CLOSE CRMNotesPullCrs
DEALLOCATE CRMNotesPullCrs


BEGIN --- data into @tempnotes temporary table

SELECT * from @tempnotes

ORDER BY employeename, companyname, entrydate

------- order by firstname --- test

END --- data into @tempnotes temporary table

END

SET NOCOUNT OFF
Post #826788
Posted Tuesday, December 1, 2009 2:05 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 1,519, Visits: 4,075
It was a valiant attempt at sample data, but unfortunately it's not quite there. Here are a few of the errors:

No insert statement for the table
Word often uses 'smart quotes' which don't translate correctly back into sql and cause issues.
You only used the # temp table for the initial check, not for the table you created.
commas before the union alls
invalid number of columns in the sample data compared to the table.

Etc. etc. I tried to fix it for a while but there were simply too many mistake to correct. You could probably actually pick up quite a bit just by figuring out how to fix all these errors in your test data. The goal is for us to be able to copy/paste/run this in SSMS and have a small example of your table's data. Massage that script(use a dev or test server) until you get it all working and you'll get a better understanding of how it works. Also, not sure if those email addresses are real or not, but you want to be really careful of actual data when you post sample data on the internet.

For your actual problem:

First, Consider using the new ANSI style joins instead of the old style
Second, I will probably get yelled at by someone for suggesting nested cursors here, so I'll throw out a disclaimer that this is almost never the best solution. Try to avoid cursors for the most part.

At a glance, it looks like you're trying to pull in all the information into a cursor then loop through that cursor. The problem with this is that each message is on a different row and you're only accessing 1 row at a time. Going with a nested cursor approach for something like this, you'd want the outer cursor to pull in just the email address/name of each individual you're sending a message to, then the inner cursor would pull all the actual notes for each one, concatenate them into the body then exit. Your parent cursor would send the message then loop.

Here's the gist of what I mean

Take your current select statement and insert it into a temp table.

SELECT...
INTO #temp
FROM...
WHERE...
order by...

Use just the emailaddress as the declaration of the outer cursor

DECLARE CURSOR... --Outer
SELECT DISTINCT emailaddress
FROM #Temp
BEGIN

Within that:

DECLARE CURSOR--Inner
SELECT...(all other fields)
FROM #Temp
WHERE emailaddress = @emailaddress
BEGIN

--Concatenate message fields

END
CLOSE / DEALLOCATE --Inner

--Send email

END
CLOSE / DEALLOCATE --Outer


Seth Phelabaum
Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #827025
Posted Wednesday, December 2, 2009 7:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
Points: 44, Visits: 162
Thanks for your response. I have fixed and posted the test data below. Also attached a text file with the same.

I am still unclear on the structure and how to code the nested cursors that you had suggested. I understand the concept - again it is the structure and coding that I am not getting. My stored procedure is also posted below.

Can you also explain and give an example of the new ANSI joins style versus what I am using now (??) ?

Thanks.



UPDATED AND FIXED TEST DATA:

------ If the test table already exists, drop it
IF OBJECT_ID('TempDB..#NOTE_tblNote', 'U') IS NOT NULL
DROP TABLE #NOTE_tblNote

---- Create the test table with

/****** Object: Table [dbo].[#NOTE_tblNote] Script Date: 12/02/2009 09:05:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE TABLE [dbo].[#NOTE_tblNote](
[NoteID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_NOTE_tblNote_NoteID] DEFAULT (newid()),
[CompanyID] [uniqueidentifier] NOT NULL,
[EmployeeID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_NOTE_tblNote_EmployeeID] DEFAULT ('{11111111-1111-1111-1111-111111111111}'),
[ContactID] [uniqueidentifier] NULL,
[OpportunityID] [uniqueidentifier] NULL,
[TaskID] [uniqueidentifier] NULL,
[CaseID] [uniqueidentifier] NULL,
[CampaignID] [uniqueidentifier] NULL,
[ProjectID] [uniqueidentifier] NULL,
[EntryDate] [datetime] NOT NULL CONSTRAINT [DF_NOTE_tblNote_EntryDate] DEFAULT (getdate()),
[NoteType] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NoteText] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NoteTextRTF] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NoteTextTrunc] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContactMade] [bit] NOT NULL CONSTRAINT [DF_NOTE_tblNote_ContactMade] DEFAULT ((0)),
[OleObjects] [image] NULL,
[BPartnerAvailable] [bit] NOT NULL CONSTRAINT [DF_NOTE_tblNote_BPartnerAvailable] DEFAULT ((0)),
[CustomerAvailable] [bit] NOT NULL CONSTRAINT [DF_NOTE_tblNote_CustomerAvailable] DEFAULT ((0)),
[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_NOTE_tblNote_CreationDate] DEFAULT (getdate()),
[ChangeDate] [datetime] NOT NULL CONSTRAINT [DF_NOTE_tblNote_ChangeDate] DEFAULT (getdate()),
[ChangeDateOffline] [datetime] NOT NULL CONSTRAINT [DF__NOTE_tblN__Chang__30E2A0C7] DEFAULT (getdate()),
[ChangedByID] [uniqueidentifier] NOT NULL CONSTRAINT [DF__NOTE_tblN__Chang__31D6C500] DEFAULT ('{11111111-1111-1111-1111-111111111111}'),
[ChangedByIDOffline] [uniqueidentifier] NOT NULL CONSTRAINT [DF__NOTE_tblN__Chang__32CAE939] DEFAULT ('{11111111-1111-1111-1111-111111111111}'),
CONSTRAINT [PK_NOTE_tblNote] PRIMARY KEY NONCLUSTERED
(
[NoteID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
USE [eCRM_crmtest1]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_CAMP_tblCampaign] FOREIGN KEY([CampaignID])
REFERENCES [dbo].[CAMP_tblCampaign] ([CampaignID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_CAMP_tblCampaign]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_CON_tblContact] FOREIGN KEY([ContactID])
REFERENCES [dbo].[CON_tblContact] ([ContactID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_CON_tblContact]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_ICOMP_tblEmployee] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[ICOMP_tblEmployee] ([EmployeeID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_ICOMP_tblEmployee]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_OPP_tblOpportunity] FOREIGN KEY([OpportunityID])
REFERENCES [dbo].[OPP_tblOpportunity] ([OpportunityID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_OPP_tblOpportunity]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_PROJ_tblProject] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[PROJ_tblProject] ([ProjectID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_PROJ_tblProject]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_SERV_tblCase] FOREIGN KEY([CaseID])
REFERENCES [dbo].[SERV_tblCase] ([CaseID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_SERV_tblCase]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_TASK_tblTask] FOREIGN KEY([TaskID])
REFERENCES [dbo].[TASK_tblTask] ([TaskID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_TASK_tblTask]
GO
ALTER TABLE [dbo].[#NOTE_tblNote] WITH NOCHECK ADD CONSTRAINT [FK_NOTE_tblNote_XCOMP_tblCompany] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[XCOMP_tblCompany] ([CompanyID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[#NOTE_tblNote] CHECK CONSTRAINT [FK_NOTE_tblNote_XCOMP_tblCompany]


----- END Create Table script

------ 7 row(s) data

------ All Inserts into the IDENTITY column
SET IDENTITY_INSERT #NOTE_tblNote ON

------ Insert the test data into the test table

INSERT INTO #NOTE_tblNote
(NoteID,CompanyID,EmployeeID,ContactID,OpportunityID,TaskID,CaseID,CampaignID,ProjectID,EntryDate,NoteType,NoteText,NoteTextRTF,NoteTextTrunc,ContactMade,OleObjects,BPartnerAvailable,CustomerAvailable,CreationDate,ChangeDate,ChangeDateOffline,ChangedByID,ChangedByIDOffline)

SELECT '335551BB-415E-48C0-A218-C7814C29A718','34B1D274-D622-428F-B11E-11247CE293BC','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','E44AACF0-C835-4A04-A96F-B8273C1700F7','NULL','NULL','NULL','NULL','NULL','2009-10-16 09:54:49.000','E-Mail Received','Original E-Mail Address: test1@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail Address: test1@dummycompany.com\par }','Original E-Mail Address: test1@dummycompany.com','0','0x00000000','0','0','2009-10-16 10:00:21.463','2009-12-01 10:30:44.073','2009-10-16 10:00:21.463','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'E9C16DE1-613F-4CBD-8651-BBD92A68A377','0617409F-5D39-4C51-9708-368407D336BD','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','D3B59963-2B28-459E-85F7-A1E4987FAAB7','NULL','NULL','NULL','NULL','NULL','2009-10-16 09:56:51.000','Contact','Original E-Mail Address: test2@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail Address: test2@dummycompany.com\par }','Original E-Mail Address: test2@dummycompany.com','0','0x00000000','0','0','2009-10-16 10:02:26.963','2009-10-16 10:02:26.963','2009-10-16 10:02:26.963','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'E36A3A02-0C67-4233-9740-16F6EE8E3D46','305299B1-81DE-4EDF-BCD5-4D82FFACDD91','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','9BC2D972-2602-457B-974F-BCCB33971493','NULL','NULL','NULL','NULL','NULL','2009-10-16 09:52:42.000','Contact','Original E-Mail address: test3@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail address: test3@dummycompany.com\par }','Original E-Mail address: test3@dummycompany.com','0','0x00000000','0','0','2009-10-16 09:58:24.323','2009-10-16 09:58:24.323','2009-10-16 09:58:24.323','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'DB1F0867-7515-4E45-AE69-29D353CBE90D','750C7124-CBBB-4D1A-B8DA-854DB2E2DF0C','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','E64A2EE8-DA05-482D-BCAA-8C4AD100C6DD','NULL','NULL','NULL','NULL','NULL','2009-10-22 14:41:15.000','Contact','Original E-Mail address: test4@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail address: test4@dummycompany.com\par }','Original E-Mail address: test4@dummycompany.com','0','0x00000000','0','0','2009-10-22 14:46:56.960','2009-10-22 14:46:56.960','2009-10-22 14:46:56.960','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT '115BBFCB-117C-4668-9BD5-1C09E777FB34','3E67D88D-042D-4E27-B65A-9DC31962B0F2','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','1A4EC154-AC62-4327-AB99-A0FD0B79BEB6','NULL','NULL','NULL','NULL','NULL','2009-10-16 09:53:53.000','Contact','Original E-Mail address: test5@dummycompany.com','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Original E-Mail address: test5@dummycompany.com\par }','Original E-Mail address: test5@dummycompany.com','0','0x00000000','0','0','2009-10-16 09:59:27.777','2009-10-16 09:59:27.777','2009-10-16 09:59:27.777','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'AD8EB582-6174-4971-9934-4D501A0D7115','957FFCD7-3077-4A99-8465-D1DF5458A146','49230AD2-14FC-4470-83BF-D07EE95EED17','9938A6C4-25AD-443E-8397-98F51446496C','NULL','NULL','NULL','NULL','NULL','2009-11-18 09:49:30.000','E-Mail Sent','Test','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 Test\par }','Test','0','0x00000000','0','0','2009-11-18 09:55:46.140','2009-11-18 09:55:46.140','2009-11-18 09:55:46.140','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111' UNION ALL
SELECT 'CA83B9A3-9099-495E-8184-985B75FCFEEC','6BCBCFC8-8E8E-49AD-87CB-FB49797005DF','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','D5641FB2-A787-493B-A7DA-96B17C12FE0E','NULL','NULL','NULL','NULL','NULL','2009-11-24 15:21:54.000','Phone Call','test1','{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 test1\par }','test1','0','0x00000000','0','0','2009-11-24 15:22:04.467','2009-11-24 15:22:04.467','2009-11-24 15:22:04.467','0F073F8D-1CDC-47B9-9F9F-087761D79D1D','11111111-1111-1111-1111-111111111111'

------ Set the IDENTITY_INSERT back to normal

SET IDENTITY_INSERT #NOTE_tblNote OFF

----- End Data Insert script



--------- START STORED PROCEDURE CODE



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 2009/11/19 rss - new
-- Procedure to pull specific CRM Notes for e-mail notification

ALTER PROCEDURE [dbo].[QVI_CRMNotesNotificationSP]

AS
SET NOCOUNT ON

--- Creates temporary @tempnotes table to hold report ouput

DECLARE @tempnotes TABLE

( entrydate datetime
, notetype nvarchar(30)
, employeename nvarchar(50)
, title nvarchar(30)
, emailaddress nvarchar(255)
, companyname nvarchar(60)
, firstname nvarchar(20)
, middleinit nvarchar(1)
, lastname nvarchar(30)
, companyid uniqueidentifier
, employeeid uniqueidentifier
, icompemployeeid uniqueidentifier
)

DECLARE

@noteentrydate datetime
, @notetype nvarchar(30)
, @icompemployeename nvarchar(50)
, @icomptitle nvarchar(30)
, @icompemailaddress nvarchar(255)
, @xcompcompanyname nvarchar(60)
, @contactfirstname nvarchar(20)
, @contactmiddleinit nvarchar(1)
, @contactlastname nvarchar(30)
, @notecompanyid uniqueidentifier
, @noteemployeeid uniqueidentifier
, @icompemployeeid uniqueidentifier
, @notecontactid uniqueidentifier
, @concontactid uniqueidentifier

, @subj nvarchar(255)
, @body nvarchar(4000)
, @temp nvarchar(255)
, @to nvarchar(1000)

, @message nvarchar(4000)

DECLARE CRMNotesPullCrs CURSOR LOCAL STATIC FOR

SELECT

n.entrydate --- Notes entry date
, n.notetype --- Note Type
, i.employeename --- Note Author
, i.title --- Note Author Title
, i.emailaddress --- Note Author E-Mail address
, x.companyname --- Company/Account associated with Note
, c.firstname --- Contact associated with Note
, c.middleinit
, c.lastname

FROM dbo.NOTE_tblNote n, dbo.ICOMP_tblEmployee i, XCOMP_tblCompany x, CON_tblContact c

WHERE

n.employeeid = i.employeeid
and n.companyid = x.companyid
and n.contactid = c.contactid
and n.notetype = 'E-Mail Sent'
and DATEDIFF(day, n.entrydate, GETDATE()) between 0 and 90 ---- variable

order by i.emailaddress

---- Start Cursor Processing

OPEN CRMNotesPullCrs WHILE 1=1

BEGIN

FETCH CRMNotesPullCrs INTO

@noteentrydate
, @notetype
, @icompemployeename
, @icomptitle
, @icompemailaddress
, @xcompcompanyname
, @contactfirstname
, @contactmiddleinit
, @contactlastname

IF @@FETCH_STATUS <> 0 BREAK

---- End Cursor Processing

BEGIN --- @tempnotes temporary table

INSERT INTO @tempnotes
(
entrydate
, notetype
, employeename
, title
, emailaddress
, companyname
, firstname
, middleinit
, lastname
, companyid
, employeeid
, icompemployeeid
)

VALUES
(
@noteentrydate
, @notetype
, @icompemployeename
, @icomptitle
, @icompemailaddress
, @xcompcompanyname
, @contactfirstname
, @contactmiddleinit
, @contactlastname
, @notecompanyid
, @noteemployeeid
, @icompemployeeid
)

END --- @tempnotes temporary table


---- new code rss 11-30-09

BEGIN --- e-mail distribution section

SET @to = @icompemailaddress

SET @subj = 'Notes Follow-up Notice'

SET @message = 'EntryDate Author E-Mail Address CompanyName FirstName LastName' + CHAR(10) + CHAR(13)

----- WHILE @@FETCH_STATUS = 0 ---- not sure if this statement is needed ????????

BEGIN

SET @temp = (select CAST (@noteentrydate AS nvarchar(12)) ) + ' ' + @icompemployeename + ' ' + @icompemailaddress + ' ' + @xcompcompanyname + ' ' + @contactfirstname + ' ' + @contactlastname + ' ' + CHAR(10)

SET @message = @message + @temp

END --- e-mail distribution section

BEGIN --- dbmail

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Job-Mail'
, @recipients = @to
, @subject = @subj
, @body = @message
, @body_format = 'HTML'

---- 'This is a test message from CRM via SQL Database Mail. This message is a reminder to follow-up on sales calls.'

--- Start debug/testing print statements

print ' '
print ' '
print 'email output start set'
print @to
print @subj
print @message
print 'email output end set'
print ' '

--- End debug/testing print statements


END --- db mail code

END -- CRMNotesPullCrs
CLOSE CRMNotesPullCrs
DEALLOCATE CRMNotesPullCrs

BEGIN --- data into @tempnotes temporary table

SELECT * from @tempnotes

ORDER BY employeename, companyname, entrydate

------- order by firstname --- for testing only

END --- data into @tempnotes temporary table

END

SET NOCOUNT OFF



  Post Attachments 
Notes Table Extract - Test Data - sql central.TXT (0 views, 14.38 KB)
Post #827488
Posted Wednesday, December 2, 2009 8:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 9:00 AM
Points: 44, Visits: 162
As a further follow-up and question:

When using the QUOTENAME script to pull/extract data from table (example script below) I had the following issues:

5 fields that had Null values for all records (OpportunityID,TaskID, CaseID, CampaignID, ProjectID)
3 fields with '0' as value for all records (ContactMade, BPartnerAvailable, CustomerAvailable)
2 fields as ntext datatype (NoteTextRTF, NoteTextTrunc)

When running the QUOTENAME script below, if it hit one of these fields then the result was one column of 'NULL'. When I commented out these fields from the script, then was able to get the correct results.

QUESTION: How to code or fix the script to allow extraction of the above fields (Null, 0, ntext values) so that the QUOTENAME script will extract all of the data correctly?

Thanks.


QUOTENAME SCRIPT:

select 'Select '
+ QUOTENAME(NoteID,'''') + ','
+ QUOTENAME(CompanyID, '''') + ','
+ QUOTENAME(EmployeeID, '''') + ','
+ QUOTENAME(ContactID, '''') + ','
+ QUOTENAME(OpportunityID,'''') + ','
+ QUOTENAME(TaskID, '''') + ','
+ QUOTENAME(CaseID, '''') + ','
+ QUOTENAME(CampaignID, '''') + ','
QUOTENAME(ProjectID, '''') + ','
+ QUOTENAME(EntryDate, '''') + ','
+ QUOTENAME(NoteType, '''') + ','
+ QUOTENAME(NoteText, '''') + ','
+ QUOTENAME(NoteTextRTF, '''') + ','
+ QUOTENAME(NoteTextTrunc, '''') + ','
+ QUOTENAME(ContactMade, '''') + ','
+ QUOTENAME(BPartnerAvailable, '''') + ','
+ QUOTENAME(CustomerAvailable, '''') + ','
+ QUOTENAME(CreationDate, '''') + ','
+ QUOTENAME(ChangeDate, '''') + ','
+ QUOTENAME(ChangeDateOffline, '''') + ','
+ QUOTENAME(ChangedByID, '''') + ','
+ QUOTENAME(ChangedByIDOffline, '''') + ','
+ ' UNION ALL'
from dbo.NOTE_tblNote
Post #827527
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse