create procedure for 'search agent'

Hey all,
Just when I thought I was starting to get the hang of T-SQL, I try a
project that now (after several failed attempts) has me thoroughly
. I'm trying to create a procedure for a 'search agent' that will
execute users' saved searches and generate email notifications via
xp_smtp_sendmail when matches are found. I want to schedule the procedure
to run as a job; and the resulting brief email to users should be in html
format as it will need to contain a hyperlink to the appropriate search
results. I think I have the basic pieces in place that I need but I could
sure use some help.
I (think) that I need to follow this basic concept to accomplish what I'm
after, but after working on this for several hours to no avail I've
reverted back to square one to try a fresh look. As a beginner this is
sometimes helpful for me in identifying the specific SQL tasks that need to
be carried out, though not yet in this case <g>. So the (very) beginnings
of the procedure, with several commented lines:
CREATE PROCEDURE dbo.usp_SearchAgent
AS
-- Need to do this for each t1.SearchID
-- in order to get the parameters to search but
-- need to then execute
SELECT t2.ParameterID, t2.FieldName, t2.SearchFor, t2.SearchOption,
t2.SearchFor2
FROM dbo.tblSavedSearch t1 INNER JOIN
dbo.tblSavedSearchParameters t2 ON t1.SearchID = t2.SearchID INNER
JOIN
dbo.tblSearchNotifications t3 ON t1.NotifyID = t3.NotifyID
WHERE (t1.NotifyUser = 1) AND (t1.NotifyExpires >= GETDATE()) AND
(DATEDIFF(d, t1.LastNotification, GETDATE())) >= t3.NotifyValue)
-- After we've executed each search
-- are there any matches?
IF ( SELECT COUNT(*)
FROM testVehicle t
WHERE ? ) > 0 -- Use the search parameters here
-- Or a better way to test..?
-- If we have matches then we need to generate an email via
xp_smtp_sendmail
-- Beyond a generic 'matches found' message the email needs to
include a link to the results (similar to, but with proper syntax):
-- '<A HREF=http://www.domain.com/searchpage.asp?
Action=AdvancedSearch&SearchID=' + SearchID + ">" + SearchName
-- Generate just a single email to the user for each SearchID with
matches
DECLARE @.TO varchar(100)
SET @.TO = ''
EXEC @.R = dbo.xp_smtp_sendmail
@.FROM = N'searchagent@.domain.com'
@.TO = N''
@.SUBJECT = N'Search Results'
@.MESSAGE = N'Matches found. Click the link below to view results.'
@.TYPE = N''
@.SERVER = N'mail.domain.com'
-- If we've sent an email then we'll need to update the
LastNotification in t1
Users' saved searches are stored in two tables (search, parameters);
another table contains the 'notification elements'; and I've included a
snipped version of the User and Vehicle tables:
CREATE TABLE [tblSavedSearch] (
[SearchID] [int] IDENTITY (1, 1) NOT NULL ,
[SearchName] [varchar] (100) NOT NULL ,
[UserID] [int] NOT NULL ,
[DateSaved] [datetime] NOT NULL CONSTRAINT [DF_tblSavedSearch_DateSaved]
DEFAULT (getdate()),
[NotifyUser] [bit] NULL ,
[NotifyID] [int] NULL ,
[NotifyExpires] [datetime] NULL CONSTRAINT
[DF_tblSavedSearch_NotifyExpires] DEFAULT (dateadd(day,7,getdate())),
[LastNotification] [datetime] NULL ,
CONSTRAINT [PK_tblSavedSearch] PRIMARY KEY CLUSTERED
(
[SearchID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, SampleSearch1, 1, 5/24/2005 00:00:00, 1, 1, NULL
2, SampleSearch2, 1, 5/23/2005 00:00:00, 1, 2, NULL
3, SampleSearch3, 2, 5/22/2005 00:00:00, 1, 3, NULL
#####
CREATE TABLE [tblSavedSearchParameters] (
[ParameterID] [int] IDENTITY (1, 1) NOT NULL ,
[SearchID] [int] NOT NULL ,
[FieldName] [varchar] (100) NULL ,
[SearchFor] [varchar] (100) NULL ,
[SearchOption] [varchar] (100) NULL ,
[SearchFor2] [varchar] (100) NULL ,
[DateSaved2] [datetime] NULL CONSTRAINT
[DF_tblSavedSearchParameters_DateSaved2]
DEFAULT (getdate()),
CONSTRAINT [PK_tblSavedSearchParameters] PRIMARY KEY CLUSTERED
(
[ParameterID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, 1, Make, Honda, Equals, NULL, 5/24/2005 00:00:00
2, 2, Model, Accord, Equals, NULL, 5/23/2005 00:00:00
3, 2, Year, 2003, Equals, NULL, 5/24/2005 00:00:00
4, 3, Make, Chev, Contains, NULL, 5/24/2005 00:00:00
#####
CREATE TABLE [tblSearchNotifications] (
[NotifyID] [int] IDENTITY (1, 1) NOT NULL ,
[NotifyFrequency] [varchar] (25) NOT NULL ,
[NotifyValue] [varchar] (3) NULL ,
CONSTRAINT [PK_tblSearchNotifications] PRIMARY KEY CLUSTERED
(
[NotifyID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, Daily, 1
2, Wly, 7
3, Monthly, 30
4, Yearly, 365
#####
CREATE TABLE [testUsers] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [varchar] (100) NOT NULL ,
CONSTRAINT [PK_testUsers] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, any@.where.com
2, any@.where.com
3, any@.where.com
#####
CREATE TABLE [testVehicle] (
[VehicleID] [int] IDENTITY (1, 1) NOT NULL ,
[Year] [int] NOT NULL ,
[Make] [varchar] (50) NOT NULL ,
[Model] [varchar] (100) NOT NULL ,
[VIN] [varchar] (17) NOT NULL
CONSTRAINT [PK_testVehicle] PRIMARY KEY CLUSTERED
(
[VehicleID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
#####
SAMPLE VALUES: 1, 2003, Honda, Accord, 12345678901234567
2, 2002, Honda, Civic, 23456789012345678
3, 2005, Ford, Mustang, 34567890123456789
4, 2002, Chevrolet, Malibu, 45678901234567890
#####
Thanks in advance for any and all assistance.
Message posted via http://www.webservertalk.comHi
Look at using sp_executesql, the following will also give you the basics to
implement a solution:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
John
"The Gekkster via webservertalk.com" wrote:

> Hey all,
> Just when I thought I was starting to get the hang of T-SQL, I try a
> project that now (after several failed attempts) has me thoroughly
> . I'm trying to create a procedure for a 'search agent' that will
> execute users' saved searches and generate email notifications via
> xp_smtp_sendmail when matches are found. I want to schedule the procedure
> to run as a job; and the resulting brief email to users should be in html
> format as it will need to contain a hyperlink to the appropriate search
> results. I think I have the basic pieces in place that I need but I could
> sure use some help.
> I (think) that I need to follow this basic concept to accomplish what I'm
> after, but after working on this for several hours to no avail I've
> reverted back to square one to try a fresh look. As a beginner this is
> sometimes helpful for me in identifying the specific SQL tasks that need t
o
> be carried out, though not yet in this case <g>. So the (very) beginnings
> of the procedure, with several commented lines:
>
> CREATE PROCEDURE dbo.usp_SearchAgent
> AS
> -- Need to do this for each t1.SearchID
> -- in order to get the parameters to search but
> -- need to then execute
> SELECT t2.ParameterID, t2.FieldName, t2.SearchFor, t2.SearchOption,
> t2.SearchFor2
> FROM dbo.tblSavedSearch t1 INNER JOIN
> dbo.tblSavedSearchParameters t2 ON t1.SearchID = t2.SearchID INNE
R
> JOIN
> dbo.tblSearchNotifications t3 ON t1.NotifyID = t3.NotifyID
> WHERE (t1.NotifyUser = 1) AND (t1.NotifyExpires >= GETDATE()) AND
> (DATEDIFF(d, t1.LastNotification, GETDATE())) >= t3.NotifyValue)
> -- After we've executed each search
> -- are there any matches?
> IF ( SELECT COUNT(*)
> FROM testVehicle t
> WHERE ? ) > 0 -- Use the search parameters here
> -- Or a better way to test..?
> -- If we have matches then we need to generate an email via
> xp_smtp_sendmail
> -- Beyond a generic 'matches found' message the email needs to
> include a link to the results (similar to, but with proper syntax):
> -- '<A HREF=http://www.domain.com/searchpage.asp?
> Action=AdvancedSearch&SearchID=' + SearchID + ">" + SearchName
> -- Generate just a single email to the user for each SearchID with
> matches
> DECLARE @.TO varchar(100)
> SET @.TO = ''
> EXEC @.R = dbo.xp_smtp_sendmail
> @.FROM = N'searchagent@.domain.com'
> @.TO = N''
> @.SUBJECT = N'Search Results'
> @.MESSAGE = N'Matches found. Click the link below to view results.'
> @.TYPE = N''
> @.SERVER = N'mail.domain.com'
> -- If we've sent an email then we'll need to update the
> LastNotification in t1
>
> Users' saved searches are stored in two tables (search, parameters);
> another table contains the 'notification elements'; and I've included a
> snipped version of the User and Vehicle tables:
>
> CREATE TABLE [tblSavedSearch] (
> [SearchID] [int] IDENTITY (1, 1) NOT NULL ,
> [SearchName] [varchar] (100) NOT NULL ,
> [UserID] [int] NOT NULL ,
> [DateSaved] [datetime] NOT NULL CONSTRAINT [DF_tblSavedSearch_DateSaved]
> DEFAULT (getdate()),
> [NotifyUser] [bit] NULL ,
> [NotifyID] [int] NULL ,
> [NotifyExpires] [datetime] NULL CONSTRAINT
> [DF_tblSavedSearch_NotifyExpires] DEFAULT (dateadd(day,7,getdate())),
> [LastNotification] [datetime] NULL ,
> CONSTRAINT [PK_tblSavedSearch] PRIMARY KEY CLUSTERED
> (
> [SearchID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, SampleSearch1, 1, 5/24/2005 00:00:00, 1, 1, NULL
> 2, SampleSearch2, 1, 5/23/2005 00:00:00, 1, 2, NULL
> 3, SampleSearch3, 2, 5/22/2005 00:00:00, 1, 3, NULL
> #####
>
> CREATE TABLE [tblSavedSearchParameters] (
> [ParameterID] [int] IDENTITY (1, 1) NOT NULL ,
> [SearchID] [int] NOT NULL ,
> [FieldName] [varchar] (100) NULL ,
> [SearchFor] [varchar] (100) NULL ,
> [SearchOption] [varchar] (100) NULL ,
> [SearchFor2] [varchar] (100) NULL ,
> [DateSaved2] [datetime] NULL CONSTRAINT
> [DF_tblSavedSearchParameters_DateSaved2]
DEFAULT (getdate()),
> CONSTRAINT [PK_tblSavedSearchParameters] PRIMARY KEY CLUSTERED
> (
> [ParameterID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, 1, Make, Honda, Equals, NULL, 5/24/2005 00:00:00
> 2, 2, Model, Accord, Equals, NULL, 5/23/2005 00:00:
00
> 3, 2, Year, 2003, Equals, NULL, 5/24/2005 00:00:00
> 4, 3, Make, Chev, Contains, NULL, 5/24/2005 00:00:0
0
> #####
>
> CREATE TABLE [tblSearchNotifications] (
> [NotifyID] [int] IDENTITY (1, 1) NOT NULL ,
> [NotifyFrequency] [varchar] (25) NOT NULL ,
> [NotifyValue] [varchar] (3) NULL ,
> CONSTRAINT [PK_tblSearchNotifications] PRIMARY KEY CLUSTERED
> (
> [NotifyID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, Daily, 1
> 2, Wly, 7
> 3, Monthly, 30
> 4, Yearly, 365
> #####
>
> CREATE TABLE [testUsers] (
> [UserID] [int] IDENTITY (1, 1) NOT NULL ,
> [EmailAddress] [varchar] (100) NOT NULL ,
> CONSTRAINT [PK_testUsers] PRIMARY KEY CLUSTERED
> (
> [UserID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, any@.where.com
> 2, any@.where.com
> 3, any@.where.com
> #####
>
> CREATE TABLE [testVehicle] (
> [VehicleID] [int] IDENTITY (1, 1) NOT NULL ,
> [Year] [int] NOT NULL ,
> [Make] [varchar] (50) NOT NULL ,
> [Model] [varchar] (100) NOT NULL ,
> [VIN] [varchar] (17) NOT NULL
> CONSTRAINT [PK_testVehicle] PRIMARY KEY CLUSTERED
> (
> [VehicleID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> #####
> SAMPLE VALUES: 1, 2003, Honda, Accord, 12345678901234567
> 2, 2002, Honda, Civic, 23456789012345678
> 3, 2005, Ford, Mustang, 34567890123456789
> 4, 2002, Chevrolet, Malibu, 45678901234567890
> #####
>
> Thanks in advance for any and all assistance.
> --
> Message posted via http://www.webservertalk.com
>|||Still struggling quite a bit with this one. For some reason things just
aren't clicking with me.
Beyond the DDL from the OP, I tried to create a view that retrieves the
correct rows (i.e. those saved searches that require a notification) which
is working properly:
#######
CREATE VIEW dbo.vw_SearchAgent
AS
SELECT t2.UserID, t2.SearchID, t3.ParameterID, t3.FieldName,
t3.SearchFor, t3.SearchOption, t3.SearchFor2, t2.LastNotification
FROM dbo.tblSavedSearchParameters t3 INNER JOIN
dbo.tblSavedSearch t2 ON t3.SearchID = t2.SearchID
LEFT OUTER JOIN
dbo.tblSearchNotifications t1 ON t2.NotifyID =
t1.NotifyID AND DATEDIFF(d, t2.LastNotification, GETDATE()) > t1.NotifyValue
WHERE (t2.NotifyUser = 1) AND (t2.NotifyExpires >= GETDATE())
GO
#######
I'm also trying to utilize a CASE expression when building the dynamic SQL,
like in this snippet:
CASE @.SearchOption
WHEN 'Contains' THEN ' AND FieldName LIKE ''%'' @.SearchFor + ''%'''
WHEN 'Starts with ...' THEN ' AND FieldName LIKE @.SearchFor + ''%'''
WHEN 'More than ...' THEN ' AND FieldName > @.SearchFor'
WHEN 'Less than ...' THEN ' AND FieldName < @.SearchFor'
WHEN 'Equal or more than ...' THEN ' AND FieldName >= @.SearchFor'
WHEN 'Equal or less than ...' THEN ' AND FieldName <= @.SearchFor'
WHEN 'Between' THEN ' AND FieldName >= @.SearchFor AND FieldName <=
@.SearchFor2'
ELSE ' AND FieldName = @.SearchFor'
END
This seems so simple: Execute each saved search against Table1 using the
appropriate saved search parameters; count the matches (if any); and
generate an email (if needed). But I can't seem to get it quite right. My
problem is with generating the correct SQL string when more than one search
parameter exists for a given SearchID.
I'm really stuck on this one, could anyone help out? Thanks.