Per Lynn's quoted reply below. Being new to SQL (my background was some light VB programming) I am unclear by what is meant by a set based solution. Most of the time I am working in SQL to derive specific rows of data meeting a specific criteria.
In the task I am currently working on, we have a table with Notes - basic fields are note entry date, note author, author's e-mail address, note type, the note itself, and the associated companyname. Normally I would search row by row for notes that meet a specified criteria, usually using a cursor. Are you stating to run a different type of query and pull all the row results into a "set"? If so, what is the difference between running a cursor and pulling rows together in a set? Sorry, but I am unclear about the "set-based solution".
Please advise.
Lynn Pettis (11/20/2009)
Not having any test data or the table structure either, here is a possible set-based solution to your cursor-based solution you are currently using.
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
);
insert into @tempnotes
SELECT
n.entrydate
, n.notetype
, i.employeename
, i.title
, i.emailaddress
, x.companyname
, c.firstname
, c.middleinit
, c.lastname
, n.companyid
, n.employeeid
, i.employeeid
--, n.contactid
--, c.contactid
--, n.entrydate --- used for entrydate conversion
FROM
dbo.NOTE_tblNote n
inner join dbo.ICOMP_tblEmployee i
on (n.employeeid = i.employeeid)
inner join XCOMP_tblCompany x
on (n.companyid = x.companyid)
inner join CON_tblContact c
on (n.contactid = c.contactid)
where
n.entrydate >= dateadd(dd, datediff(dd, 0, getdate()), -10) -- Notes entered 10 days ago
and n.entrydate < dateadd(dd, datediff(dd, 0, getdate()), -9) -- but not 9 days ago
select
*
from
@tempnotes -- what data was entered